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:
To set the database to simple recovery, either use SSMS or run the following TSQL against the database:
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:
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)