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 ?

  • What types of backups are you running and what is the SQL recovery model set to? – sgmoore Apr 6 '10 at 13:01

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


    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)

  • Thank you so much for your help with this. So if I implement the second option - will it be ok if I have a normal BAK file from a few days ago ? It won't affect any restores I may have to do ? (I don't need to go to the n'th degree on restoring). – user33373 Apr 6 '10 at 13:20
  • @Scott: you can restore back to that *.bak file you have - no problem. You just won't be able to recover anything that happen since then (that would be in the transaction logs that you purge away) – marc_s Apr 6 '10 at 13:36
  • Great. Thanks for that. Very much appreciated. – user33373 Apr 6 '10 at 13:48
  • 1
    With TRUNCATE_ONLY, you're paying the cost of full recovery without reaping the benefits. If you need full recovery, then you need to take real log backups. If you don't, then switch to simple recovery. Please read Paul Randal's articles here and here, they are a little long but easily digested and really help to shed light on how SQL Server backups work. – Stephen Jennings Sep 4 '10 at 22:32

Summary: If you find yourself in this situation, you probably want Option 1 below. Skip there to fix the problem, or read on for more explanation.

A database in Microsoft SQL Server has different "recovery modes" it can be configured to be in:

  1. Simple. You may take both full backups and differential backups of a database in simple recovery mode. A full backup contains the entire database at a point in time, while a differential backup contains everything that has changed since the last full backup.

    A backup plan for such a database might be "A full backup every Sunday, plus a daily differential backup". This keeps the differential backups relatively small, since they only contain data changed since the previous Sunday. In the event of a crash, you lose no more than 1 day's worth of data.

  2. Full. You still may take both full and differential backups, but you also must take periodic transaction log backups. Whereas differential backups contain database changes since the last full backup, log backukps only contain data since the last log backup, so log backups should be small and quick.

    A backup plan for a full recovery database might be "a weekly full backup, a daily differential backup, and a transaction log backup every 5 minutes". This means you never lose more than 5 minutes of data. When restoring such a database, you would restore to the latest differential backup, then apply every log backup made since that point.

  3. Bulk logged. This is sort of like "full recovery mode, except when I say otherwise".

Full recovery is great because you can restore your database nearly up to the time of a crash, but it also necessitates that you take log backups. Why? Because logs inside the transaction log file are only marked as "truncated" once you take a log backup. Once a log is truncated, new logs are allowed to overwrite it.

If you never take a log backup, then nothing in the transaction log is ever marked as truncated, and therefore the log file must grow with every database change.

For more information on how the transaction log works, please read Paul Randal's excellent articles:

Now, in the situation where you find yourself with an enormous transaction log, there are two real options:

Option 1: Switch to the simple recovery model

You probably don't need to take log backups (since you aren't doing it now), so you can just switch to simple recovery model:


This will mark everything in the transaction log as truncated, so the log file won't get any bigger. Once you've done that, to shrink the physical log file:


This means the log file will truncate itself automatically after every transaction. Therefore, its size will not grow out of control. You will probably not have to manage the transaction log at all; however, you lose the ability to restore your database beyond your latest full or differential backup.

Option 2: Start taking transaction log backups

If you really do need full recovery, then you must start taking transaction log backups.

First, to get yourself out of the bad situation, you want to truncate the entire log, then shrink it:


Now your transaction log file should be a reasonable size. It will start growing again immediately; though. You must now start taking log backups regularly. You can do this through a maintenance plan, or through regularly running a command such as:

BACKUP LOG [MyDatabase] TO DISK = N'C:\Backups\MyDatabase_Log_Backup1.bak' WITH INIT

Side note

DBCC SHRINKFILE is not meant to be used regularly:

  • Log files will grow back to a stable sized based on database activity and how frequently you take log backups.
  • Shrinking data files completely fragments your indexes.

If you find yourself shrinking log files on a regular basis, you should probably either switch to the simple recovery model, or take more frequent log backups.

  • I wish I had more than just one vote to give. – Lee Jun 25 '11 at 22:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy