|
|
Posted by Erland Sommarskog on 11/24/06 09:34
sifrah (sifrah@walla.com) writes:
> My SQL server transaction log is getting bigger every day and my HDD if
> running out of space.
So that is the first question: why does the log keep growing?
That leads us to the next question: if the database crashes, do you need
up-to-the-point recovery? Or would you be content with restoring the latest
backup, and lose some data?
In the first case, you should run with full recovery, and backup your
database and transaction log regularly. If you never backup the log, it
will just grow and grow.
If you don't care about up-to-the-point recovery, set the recovery mode
to simple. SQL Server will then truncate the transaction log regularly.
It can still grow if you run a huge transaction, as the log is never
truncated past any active transaction. Note that truncate and shrink is
not the same thing.
If you have a log that just keeps growing, I suspect that you run with
full recovery, but never back up the transaction log.
> So i follow the MS KB about how to Shrinking the Transaction Log.
> After doing so the log is much much smaller as i can see the size of it
> under enterprise manager.
>
> The problem is that the HDD still shows the same size.
> If i shrink the DB why the and reduce its size why the HDD does not
> shows it?
What do you mean with the HDD shows the same size? Has the size of the
file changed, but Explorer still shows the same amount of used space?
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|