SQL Server 2005, Huge LDF file

sql-server-2005

I have a database running on SQL Server 2005. The database is 20GB and the LDF file is 35GB ! I am 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 ?

Best Answer

Well, basically, your SQL Server Log files need to be backed up regularly - every couple hours or days. When you do that, they'll shrink.

Now, in your case, there's two things you can do:

  • switch the "recovery model" of your database to "simple". What that means is: you'll be able to restore the database to the last full or differential data backup, but nothing since then - you'll have less logs, though

  • use 

    BACKUP LOG (yourDB) WITH TRUNCATE_ONLY

    to truncate the logs right away (you'll lose the ability to do a restore to anything back in time between the last data backup and now)

Related Question