SQL Server 2005 HUGE ldf file

sql-server-2005

I have a database on SQL Server 2005 which is 24Gb. The ldf file which goes with it is now 47Gb. I cannot run a backup of the database because of lack of disk space.
My question is : how do I get this ldf file down much lower (or even zero) and how do I keep it from getting too big in the future ? The database has been backed up using Brightstor but the ldf is still massive.

Thanks for your help.

Best Answer

Assuming your database / application is performing normally and doesn't do that many updates. And assuming you are not doing any transaction log backups, then you need to do one of two things:

  • Either start doing transaction log backups (in addition to the database backups)
  • Or (as has been suggested) switch your database to Simple recovery mode

To set the database to simple recovery, either use SSMS or run the following TSQL against the database:

ALTER DATABASE [DBNAME] SET RECOVERY SIMPLE;

Once your database is using the Simple recovery model, make sure you take a full backup. Following that, you will need to tell SQLServer to explicitly shrink the log file by running the following TSQL:

DBCC SHRINKFILE (FileName, TargetSize)

where the filename is the name of the database log file (i.e. DBNAME_Log) and the target size is in megabytes (keep in mind that you don't want to make it too small otherwise it will just have to grow again to the 'normal' level)

Related Topic