Optimizations in protecting SQL databases with high churn by DPM

This article discusses two specific problems that you may encounter while protecting high churn databases using DPM. Both of these concern the amount of disk space used by the DPM log files in certain scenarios and how to you can mitigate the issues that can arise as a result.

When DPM initiates an incremental backup for a SQL database, it instructs SQL to dump all transaction log entries that were generated since the last incremental backup to a temporary log file. DPM then copies the new log file to the DPM server to make a new recovery point. One of the issues with this behavior is that sometimes the temporary transaction log file(s) can become extremely large and take up a considerable amount of space on the SQL server where the SQL DB is being stored. You can work around this by replacing the folder location where the temporary transaction logs are currently stored with a symbolic link pointing to a cheaper disk location, or to one with more available free space.

The temporary transaction log files are stored in a folder named “DPM_SQL_POTECT\” + “MachineName”+“SQL Server instance name\” + “Database name” + “_log.ldf\Backup\”. This folder is created in the same location as the log definition file location. The DPM_SQL_PROTECT folder can be replaced by symbolic link that points to a cheaper disk location or one with more available free space.

1. Move (cut and paste) C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DPM_SQL_PROTECT to E:\, then (optionally) rename the moved DPM_SQL_PROTECT folder to another name. In this example it was renamed to E:\BackupTest.

NOTEIf you have multiple SQL instances or multiple different paths to the database log files, you will want to repeat these steps for each set of temporary logs you want to be redirected by using multiple linked folders.

2. Run the command below to remake the DPM_SQL_PROECT folder and link it to the E:\BackupTest folder.

Once this is complete, all temporary SQL log files will be redirected to the path under E:\BackupTest, thus freeing up the disk space that was required on C: volume.

Issue 2: The transaction log might fill up due to high churn, leading to transaction failure.

This issue can be avoided by placing an appropriate check on the transaction log space being filled. Whenever the transaction log space is about to fill, flush the transaction logs to the place where the backup job expects them to be located. Here’s an example:

In this example, we create an alert that can flush the transaction logs when the transactions log space is about to be full, then copy them to a location where the DPM job can find the logs.

The example below assumes that the symbolic link mentioned above has been created. If this is not the case, replace “E:\BackupTest” with the location of “DPM_SQL_Protect”.