DPM 2010/MS SQL 2005 shrink log files

Recently my company started using Data Protection Manager 2010 which in my opinion is a superior product when it comes to backing up Microsoft products such as Exchange and MS SQL.

For a few months we have been backing up our MS SQL 2005 databases using DPM. I noticed that one of our large databases had a log file of 12 GB. Before using DPM a maintenance plan would backup the log and shrink the file weekly. After we started using DPM this has not happened.

So I started investigating how this should be handled when backing up the database using DPM 2010 and the answer was not as I expected.

If you only want the short answer then it comes here: You do not shrink the log file on a regular basis!

If the short answer is not satisfactory then continue on reading. To understand the reason that you should not shrink the log please consider the following analogy.

Your house has a leaking roof. You put a bucket for the water under the leak (the bucket is the transaction log). When the bucket is full you need to empty the bucket (the log file is full and you need to make a backup in order to truncate/empty the file).

Emptying the bucket will not reduce the size of the bucket (backing up the transaction log will not reduce the size of the LDF file).

If you for some reason wants to modify the bucket in order to make it smaller this can be done, but it will be quite time consuming. Furthermore the bucket will now fill more quickly and you will have a problem. You need to expand the size of the bucket making sure that no water is spilled. (You can shrink the log file but the log file will reach its limit more quickly and the file must be expanded. This is a time consuming operation).

I would choose a bucket that has a size so that it does not fill too quickly, but it also should not take up too many square meters of the floor in my house. (The log should be large enough so that auto growth is not necessary between log backups – but still the log should not take up all of the disk space – find a proper balance).

So my conclusion to my problem was that a log file of 12 GB was not an issue. The log was being truncated on a regular basis and the file size has so far not exceeded 12 GB. I have 200 GB of free space on the disk. So shrinking the file does not make much sense.

Check out msdn for further reading about transaction logs, truncating and shrinking files