SQL Server 2005, Huge LDF file.

[diskmonitor]

Hi,

I have a database running on SQL Server 2005. The database is 20Gb and the LDF file is 35Gb ! I'm now running low on disk space and want to shrink the log file. How can I do this and how can I stop this happening again ?

Many thanks

Scott

 

[SQLSister]

Your problem is that you are NOT backing up the log. Backining up the database isn't enough, you need to regularly backup the log.

So first, do you have a good current backup of the database? If not do that before anything else. If you still have space, backup the transaction log as well.

Then truncate the log and shrink it (see BOL on BACKUP LOG WITH TRUNCATE_ONLY and shrinking transaction logs) Note LOG WITH TRUNCATE_ONLY will not work on 2008, you need to change the database to simple recovery mode instead.

Then set up regular backups of both the logs (and database if you didn't have them). We backup our logs every 15 minutes or so.

"NOTHING is more important in a database than integrity." ESquared