Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 ?

share|improve this question
    
What types of backups are you running and what is the SQL recovery model set to? –  sgmoore Apr 6 '10 at 13:01
add comment

2 Answers

up vote 2 down vote accepted

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)

share|improve this answer
    
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
add comment

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

ALTER DATABASE [MyDatabase] SET RECOVERY SIMPLE

Now shrink the log file to get yourself out of the bad situation:

DBCC SHRINKFILE N'MyDatabase.ldf'

Essentially, this means the log file will truncate itself automatically after every transaction. Therefore, its size will not grow out of control. You will 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. I suspect

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

BACKUP LOG [MyDatabase] WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE N'MyDatabase.ldf'
GO

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

You'll notice we used a command DBCC SHRINKFILE. Please don't think this command is a magic bullet; in normal usage you should almost never need to shrink a log file (it's just going to grow again anyway, back to a stable size based on database activity and how often you run log backups), and you definitely should not be shrinking your data file regularly (shrinking the data file completely fragments your indexes).

share|improve this answer
    
I wish I had more than just one vote to give. –  Lee Jun 25 '11 at 22:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.