Shrinking SQL Server 2005 log files – part deux

In Shrinking SQL Server 2005 log files I gave some standard approaches to shrinking transaction logs. I was having trouble making those work on a development server, due in part to an extreme shortage of disk space. I came up with a neat little trick that seems to work pretty well, with some caveats.

The trick is to change the database recovery model from Full to Simple, change the recovery model back to Full, then do the dbcc shrinkfile. (The recovery model is found in the database’s properties in SQL Server Management Studio.) I think this works because changing the model to simple clears the committed transaction log entries, effectively emptying the transaction log if it is not actively being used (in which case this is probably not a good idea anyway). I should note that in case Something Bad Happens™, it would be prudent to take a backup of the database first if possible.

Hopefully the combination of the previous article and this little trick will help you get those transactions logs back under control.

Advertisements
This entry was posted in SQL Server. Bookmark the permalink.

2 Responses to Shrinking SQL Server 2005 log files – part deux

  1. Pingback: Shrinking SQL Server 2005 log files « BDBits Bytes

  2. NCHC IT says:

    This solution has resolved a critical issue for us. As it turns out, with the database in question (Rightfax), I have to perform these steps every couple months as the log file simply will not shrink otherwise.

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