Handling huge LDF files when SQL Recovery Mode is ‘Simple’

Recently, I was reviewing a space issue on a server, only to find that the culprit was a 35GB LDF.

It was the OpenManage Essentials database.  I’ve been trying to qualify it as a method of detecting hardware issues on ESXi hosts and various Dell hardware (Powervault SAN, etc).

I reviewed the recovery mode and was surprised to find it was already set to Simple.

I ran a backup of the database via SQL Mgmt Studio to see how the log file would respond- it was unchanged.

As I’m not a SQL guru, I did a bit googling on the issue and came across various articles, including this one which outlines things pretty well.

For my scenario, I’m really not concerned with missing transactions in restoring from backup- latest would be fine, so Simple recovery really is a good fit. For Simple recovery, all I really needed to do is run shrinkfile on the log database:

sql1.png

Make sure to select Log for File type.

sql2.png

Clicking OK reduced the size from 35GB to 1GB in a minute or so.

This is obviously not a one-size-fits-all solution for large ldf’s. As mentioned in the referenced link above, If you’re using full recovery mode or can’t break your transaction chain, your approach is different.

 

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s