Shrinking large Log files in SQL 2008 R2

A common problem encountered with installations of Dynamics SL at locations that either don’t have a full time DBA or where the DBA is not responsible for the Dynamics SL databases is VERY large (i.e. sometimes several times larger than the database files themselves) “Log files” for the Dynamics SL database on the SQL Server.

Why Doesn’t the “Shrink Database” command seem to work?

You may have seen and tried a “Task” option in SQL Management Studio that says “Shrink”. If you’ve tried running this without seeing any reduction in your log file sizes, you’re probably confused and you probably were unaware that this Shrink process will only be able to reduce the size of your log files if either your database is in “Simple” Recovery Model OR you have recently, successfully create a transaction log backup on the database. This blog post does not go into the specifics of why all this is true. Perhaps I’ll do another blog later to fill in the “whys” about all this. For now, we will focus on the “hows”.

How to check Database and Log file sizes

You can check the the size of your database and log files for a database either from SQL Management Studio or through some simple SQL Queries:

ShrinkDB_Image02


SELECT
DB_NAME(database_id) AS DatabaseName
,Name AS Logical_Name
,Physical_Name
,(size*8)/1024 SizeMB
FROM
sys.master_files
WHERE
DB_NAME(database_id) = 'TEST_TTDSAPP'

What usually causes overly large log files?

Excessively large log files are usually the result of a combination of 2 aspects of your SQL environment:

  1. having the database setup in “Full Recovery” mode
    • Note: This is usually NOT a bad thing and in fact is usually recommended, it’s only problematic when it exists WITH item 2 below
  2. Not have SQL Transaction Log backups occurring regularly
    • Either there is no maintenance plan or the maintenance plan doesn’t include log file backups
      or
    • For some reason the transaction log backup step of the maintenance plan has failed to execute for some extended period of time

How do I tell if my database is in Full Recovery Model or not?

You can check the recovery model of your database from SQL Server Management Studio.  Right mouse click on the database in question, and select “Properties” from the floating menu.  Then select “Options” in the “Select a page” list and look for the “Recovery model”.  Setting.  If you have inordinately large log files, then most likely this setting will say “Full”.

ShrinkDB_Image01

 How do I get my log files to shrink right now?

Method 1 – change the recovery model to “simple”, backup, shrink, change back to “full”

One method for getting your database log files to shrink to change the recovery model for the database from “Full” to “Simple” temporarily, then perform a full backup of the database and a Shrink of the database, then set the recovery model back to “Full”

The step-by-step for this is:

      • Change the recovery model from Full to Simple
        1. Open SQL Server Management Studio and attach to the appropriate server
        2. Locate the database in question and right mouse click to and select “Properties” from the floating menu
        3. In the “Database Properties” dialog box, Select the “Options” page
        4. Change the “Recovery model” setting from “Full” to “simple”
        5. Click OK

ShrinkDB_Image02

      • Perform a Full database backup
        1. right mouse click to and select “Tasks” | “Back Up…” from the floating menu
        2. In the “Back Up Database” dialog box, Set “Backup Type” to “Full”
        3. Select an appropriate destination location for your backup file
        4. Click OK

ShrinkDB_Image03

      • Shrink the database
        1. right mouse click to and select “Tasks” | “Shrink” | “Database” from the floating menu
        2. Click OK

ShrinkDB_Image04

      • Change the recovery model back from Simple to Full
        1. Open SQL Server Management Studio and attach to the appropriate server
        2. Locate the database in question and right mouse click to and select “Properties” from the floating menu
        3. In the “Database Properties” dialog box, Select the “Options” page
        4. Change the “Recovery model” setting from “Simple” to “Full”
        5. Click OK

ShrinkDB_Image02

Method 2 – Backup the transaction logs then shrink the database

You can leave your database in Full Recovery Model and still shrink your database. You simply need to make sure that you have recently successfully created a transaction log backup.

The step-by-step for this is (Note: I’ve occasionally found that I need to perform these steps twice though never identified why):

      • Perform a Transaction Log backup
        1. right mouse click to and select “Tasks” | “Back Up…” from the floating menu
        2. In the “Back Up Database” dialog box, Set “Backup Type” to “Transaction Log”
        3. Select an appropriate destination location for your backup file
        4. Click OK

ShrinkDB_Image05

      • Shrink the database
        1. right mouse click to and select “Tasks” | “Shrink” | “Database” from the floating menu
        2. Click OK
          ShrinkDB_Image04
Posted in Dynamics SL Tips Tricks and examples.