1

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.

4 Answers 4

3

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)

0

The first thing that comes to mind would be, how is what is the logging mode set as on your database? If you change it to simple it should reduce the size of this.

This link may help: http://www.mindsdoor.net/SQLAdmin/TransactionLogFileGrows_1.html

2
  • Would this have a huge impact on recovery options ?
    – Scott Jackson
    Jun 28, 2010 at 10:17
  • 1
    Depends on your backup schedule. Transaction log in your current setup would be trimmed if you would use Grandfather&Father&Son backup. Which means Monthly Full backup, Weekly Full backup, and then every day incremental backup. If you don't run incremental backups your sql log will sky rocket. So if you run like Full Backup every day you can switch it to simple recovery mode and you should be fine.
    – MadBoy
    Jun 28, 2010 at 10:24
0

If the backup is failing due to lack of space it sounds like your backing up to the same drive as you have your logs (and possibly data?). Hopefully Brightstor is backing up to an off server location?

Do you need log backups for recovery? If not, switch to SIMPLE recovery mode and then shrink the transaction log files.

If you do need log backups you can reclaim space by running a log backup with TRUNCATE_ONLY followed by a log shrink and then a full DB backup so that you can start up you backup chain again.

Given that your ldf is huge I imagine that your simply not taking log backups with the DB in FULL recovery mode hence it will keep on growing until you either back it up or switch to SIMPLE recovery.

0

You need to set your database logging to simple (but only if you do Full Backups of your database!).

  1. Right click on your database
  2. Choose Properties
  3. Choose Options
  4. Set Recovery mode to simple

This will work and is best if your backup schedule is Full Backup every day. Because in such scenario your log won't be trimmed and it will skyrocket.

If you would be using Grandfather&Father&Son backup technique. Which means Monthly Full backup, Weekly Full backup, and then every day incremental backup. Then for that you need Full Recovery Mode.

So in your case if you run Full Backup every day you can switch it to simple recovery mode and you should be fine without risking your data. If you plan to use incremental then leave it at Recovery Mode Full.

You must log in to answer this question.