HDD still full after shrinking transaction log

[SIMONI]

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

 

[mrdenny]

When you shrink the log, it doesn't remove any data from the log.

You need to backup the log (you will need disk space to hold the backup).

If you don't care about keeping the data in the log, then you need to do a few things.

First wipe the log.

backup log {YourDatabase} with truncate_only

Then shrink the log.

use {YourDatabase}
go
dbcc shrinkfile ({YourDatabase}_Log, 100) /*The number is the number of Megs to leave the file, this can be changed as needed.
go

If you don't care about keeping the transaction log for point in time restores, change the recovery mode from full to simple.

alter database {YourDatabase}
set RECOVERY SIMPLE
go

You should now to a full backup of the database.

backup database {YourDatabase} to disk='d:\YourDatabase.bak' with init

If you don't change the recovery mode from full to simple you will need to setup regular transaction log backups (every couple of hours at least) so that the log doesn't fill up again.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SIMONI]

Thanks, I did it and it's all good now.

Thanks Again.

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com