SQL Server 2005, Huge LDF file

Question

I have a database running on SQL Server 2005. The database is 20Gb and the LDF file is 35Gb ! I'm 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 ?

Many thanks

Scott

Answer 1

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)

Answer 2

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:

• Understanding Logging and Recovery in SQL Server
• Importance of proper transaction log size management
• Misconceptions around the log and log backups: how to convince yourself

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).