Home > SQL Server > SQL Server 2008 /2008R2 : Shrink LDF Files

SQL Server 2008 /2008R2 : Shrink LDF Files


This is a common issue, by design, LDF files associated to databases are not shrink, and size may became huge. Sometime, you have to manually shrink them. Here is how to do:

First : DO NOT CHANGE recovery mode to simple, then turning back to “Full”. This is a heavy operation that under (pretty rare indeed) circumstances may corrupt some data.

Instead, do the following:

  1. Backup transactional log via SQL Server Manager (Right Click>Tasks>Backup> choose backup Type: Transaction Logs)

This will, backup and flush the LDf file, now you have to do a checkpoint. This will move the active section of log to move at the beginning of it.

  1. To achieve this Checkout, start a “new Query” windows after selecting database, and simply type “Checkpoint
  2. Now, backup transaction Log again (you can use same file as the first backup)
  3. Shrink file using right click>Tasks>Shrink>File and specify Log as file Type and hit Release unused space radio button

Click OK, the file is now shrinked 😉

Thanks to Christian Robert : http://blogs.developpeur.org/christian/archive/2011/10/03/sql-server-faq-sql-pourquoi-mon-fichier-de-log-ldf-est-il-aussi-gros-comment-diminuer-sa-taille.aspx

What about the simple recovery mode?

When you perform a SharePoint Backup, SQL is making full backup. That kind of backup do not shrink LDF file, and SharePoint native backup do not handle transaction log. So, if you are only using Sharepoint tools (backup-SPSite/Backup-SPFarm cmdlet or Central Administration) to bakup youre far, you can safely turn youre database to a simple recovery mode. But if you have a more powerful backup solution (such as Microsoft Data Protection Manager) please consider Full recovery mode and en maintenance plan that will periodically perform transaction log backup.

More Info:

Article URL
Checkpoints and the Active Portion of the Log http://msdn.microsoft.com/en-us/library/ms189573.aspx
Plan for backup and recovery (SharePoint Server 2010) http://technet.microsoft.com/en-us/library/cc261687.aspx
SQL Server Recovery Model Overview http://msdn.microsoft.com/en-us/library/ms189275.aspx
Advertisements
Categories: SQL Server
  1. user
    March 6, 2013 at 5:08 pm

    I am not able to do step 1 – backup the transaction logs, because my log file is too big and there is not enough disk space to do a backup. What are other options? Set from “full” to “simple” and then back to “full” – what does that do?

    • March 8, 2013 at 12:27 pm

      Well it’s not the state of the art, but it works ! You can switch the recovery model to simple, then you should be able to shrink log file. After that you may reconsider using full or simple recovery mode. Chances that something get wrong while changing recovery mode are pretty thin.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: