HDD still full after shrinking transaction log

[sifrah]

Hi All,
My SQL server transaction log is getting bigger every day and my HDD if
running out of space.

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?

Is there a way to clear the size from the HDD?

Thanks All

[Erland Sommarskog]

sifrah (si****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[sifrah]

Dear Erland,
Thanks for the informative response.
The old administrator didn't back up the DB and log on a regular basic
and that why I got all this mass.

To resolve the issue, i did a backup for the DB and log a few times
until the space cleared from the HDD.

To prevent the issue from happening again i set a maintenance plan to
do a backup every night on top of my backup exec backups.

Do you think it's ok?

Thanks

Erland Sommarskog wrote:

sifrah (si****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

sifrah (si****@walla.com) writes:

Thanks for the informative response.
The old administrator didn't back up the DB and log on a regular basic
and that why I got all this mass.

To resolve the issue, i did a backup for the DB and log a few times
until the space cleared from the HDD.

To prevent the issue from happening again i set a maintenance plan to
do a backup every night on top of my backup exec backups.

Do you think it's ok?

You did include a backup of the transaction log in the maintenance plan,
I hope?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx