2 comments

SCDPM: Backup SQL and Truncate SQL Logs

Published on Friday, December 23, 2011 in ,

First off it’s been a while since I added a new post. I’ve been busy for work with non technical stuff… So less war stories to blog about :( Also I’ve got zero practical experience with SCDPM, all I found out below is from my google skills.

Lately one of my customers had a FIM Service service outage. Quickly we found out the SQL transaction log was completely filled up and had no more room to expand… Not really a healthy situation. They have System Center Data Protection Manager in place which is configured to take backups on a regular base. Now what we were seeing is that the SQL log was never releasing any space. So the free space within the file became smaller and smaller.

Now for the sake of clarity, in a typical scenario if you take a backup of a DB you are supposed to make sure the log is truncated. Do not confuse this with shrinking. Shrinking is making sure the log file itself is smaller in size. Shrinking is something which you don’t have to do on a scheduled based. Truncating on the other hand is making sure there's more free space within the file. If you fail to do this the file will keep on growing and growing regardless the size of your database.

As a side note, for this to work the database has to be in full recovery mode, if that’s not the case transaction logs aren’t stored anyhow. And you don’t need to do anything specific.

clip_image002

This is how the backup was configured before our changes:

clip_image001

This was obviously not good as we had a full log file. At first sight I didn’t found anything in the DPM GUI so I did some research and I came up the following statements:

Have you scheduled to take incremental backups for the SQL Server databases? Express full backups do not truncate logs. Incremental backups which are in fact log backups truncate logs. [http://msgroups.net/microsoft.public.dataprotectionmanager/DPM-transaction-logs-truncation-on-SQL-Server]

Ok, so we need Incremental backups, now where’s that checkbox…

SQL will truncate the logs files after each DPM Synchronization (incremental backup). However truncation is not the same as shrinking. Once a log file grows, you will need to shrink it manually. If the time between synchronizations is set for something like 12 or 24 hours, then the log file has already grown and you will need to shrink it manually, then reduce the synchronization period to keep it to a reasonable size. If the synchronization selection is ‘Just before a recovery point’ then incremental backups won’t get scheduled. This option is a way of telling DPM, that the user is interested only in express full backups and not incremental backups. [http://social.technet.microsoft.com/Forums/en-US/dpmsqlbackup/thread/f81f0ea7-cfd6-4e8f-a3e3-9ae4d207eabd]

So the following setting was modified:

clip_image003

Here’s a screenshot of the log file size before:

clip_image005

And here after one of the synchronization runs, you can clearly see that the log file has been truncated and thus the free space is nearly 100%. As expected! As we set the interval to 15’ this happened really fast.

clip_image007

Related Posts

2 Response to SCDPM: Backup SQL and Truncate SQL Logs

Anonymous
12 April, 2012 16:54

this looked promising, but you switched the words truncate and shrink on several locations, also, the first screenshot you took is about the data file, the second is about the logfile.

12 April, 2012 20:28

Hey! Thanks for your correction. The first screenshot is obviously wrong. That's the problem with those screenshots, you have to take them onsite while working at a customer. Sadly they don't pay me to blog, so errors sneak in. Should have seen it when writing the post, but obviously I took the screenshot for granted. SQL is definitely not my core business, but I'm wondering where I switched the truncate/shrink terms. I re-read it and don’t see anything wrong. Could you elaborate on that? That would allow me to correct the article so others can benefit. Thanks!

Add Your Comment