DPM–SQL transaction logs do not truncate

This week my customer asked me to solve a DPM/SQL issue where the SQL log files did not truncate after a successful backup through Data Protection Manager. After doing some investigating on the internet I’ve found a lot of blog posts and forum threads that offered a solution.
To my surprise however, some of those offered solutions did not even exist! For example, one of those described a solution to change the backup type from “Express Full Backup” to “Incremental Backup” when the later doesn’t even exist!

In my case, the problem was actually consisting out of three causes where the problem description of two differs just a little from each other.

Problem 1
Although DPM says the backup went fine, never have the SQL log files been truncated.

Cause 1
When DPM creates a backup the Windows Server Backup mechanism is used, among others for letting SQL know that the log files can be truncated.

Resolution 1
Enable the Windows Backup features on the target server.

 

Problem 2
Although DPM says the backup went fine, suddenly the SQL log files do not truncate anymore.

Cause 2
When DPM creates a backup of the log files, it requires that the same amount of free space needs to be available on the disk used to store those log files. So when you create a backup of a SQL log with the size of 7GB, an additional 7GB of space needs to be free on that disk.

Resolution 2
Free up some space on the disk and do the backup again. If, for any reason, the log files are growing extremely fast you may consider doing an Express Full Backup more often or simply increase the size of the disk where the log files are stored.

 

Problem 3
Both the DPM backup failed and the SQL logs do not truncate anymore.

Cause 3
DPM doesn’t like it when a recovery model on a SQL database is changed. In my experience, some database managers like to change the recovery model from ‘full’ to ‘simple’ when they perform some maintenance/tasks on the database.
I found that the recovery model needs to be set to ‘full’ for DPM to do it’s work properly.

Resolution 3
Make sure the recovery model is set to ‘full’ (SQL) and re-add the database to the protection group (DPM).

One comment

  1. Guest says:

    Great post!  Very helpful.  Thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *