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.

This is how the backup was configured before our changes:

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:

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

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.