I was working recently on System Center 2019 Data Protection Manager (SC DPM) which is protecting different SQL databases with a high Churn rate. Churn is the amount of new data every day (that is, written or appended to existing databases). If you are protecting SQL databases with high churn using DPM, then you need to optimize your SQL sever to avoid backup failure.
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 location named “DPM_SQL_PROTECT“. DPM then copies the new log file to the DPM server target storage to make a new recovery point.
The files under the “DPM_SQL_PROTECT” reflect the changes inside the current .ldf file and the file size will be approximately as large as the % of log space used inside the .ldf file at the time of the backup. That temporary log file will grow/shrink based on log usage between incremental backups. 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.
When an express full backup or incremental synchronization is running, the content of the folder “DPM_SQL_PROTECT” will be temporarily used to copy the transactions log files and then deleted. The problem is when the Log-Drive is full, the SQL-Server backup is not working anymore.
In this blog post, I will show you how to change the “DPM_SQL_PROTECT” folder location to another volume with more available free space to avoid backup jobs from failing. This is very important when you are protecting a high churn SQL databases using DPM.
Change “DPM_SQL_PROTECT” Folder Location
As the first option, you can consider increasing your frequency of Incremental backups in case your database has a high churn rate. That would cause the log truncation and backup to be more frequent and dampen or even prevent unnecessary growth of the log file.
What about if you don’t want to increase the frequency of Incremental backups? read on…
To work around this behavior, you can replace 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.
Please note that the temporary transaction log files are stored in a folder under the following structure: “DPM_SQL_PROTECT\”+”Server Name”+”SQL Server Instance Name\”+”Database Name”+”_log.ldf\Backup\“. This folder is created in the same location as the log definition file location.
In this example, I have the following environment:
- Server Name: MABSV3-WS2016
- SQL Server Instance Name: MABSV3DB
- The database protected by DPM: DPMDB
- The log definition files are stored at C:\Program Files\Microsoft Azure Backup Server\DPM\DPM\DPMDB
- The temporary transaction log files are stored under: C:\Program Files\Microsoft Azure Backup Server\DPM\DPM\DPMDB\DPM_SQL_PROTECT\MABSV3-WS2016\MABSV3DB
- The new volume with enough free space is mounted under the G:\ drive.
Now take the following steps:
- Right-click and choose Cut on the “DPM_SQL_PROTECT” folder under C:\Program Files\Microsoft Azure Backup Server\DPM\DPM\DPMDB based on this example, then paste it under the G:\ drive. You can also rename the folder that you paste on the G:\ drive. In this example, I renamed it to SQL_DPMBackup.
- Finally, open a command prompt and run the following command to remake the “DPM_SQL_PROTECT” folder and link it to G:\SQL_DPMBackup folder: mklink /D “C:\Program Files\Microsoft Azure Backup Server\DPM\DPM\DPMDB\DPM_SQL_PROTECT” “G:\SQL_DPMBackup”
Once you run the command above, all temporary SQL log files will be redirected now to the new path under G:\SQL_DPMBackup, thus freeing up the disk space that was required on C: volume. As showing in the following screenshot, the DPM_SQL_PROTECT on the C: volume has a link symbol that is redirecting to G:\SQL_DPMBackup folder.
Please note that if you have multiple SQL instances or multiple different paths to the database log files, you will want to repeat the same steps above for each set of temporary logs you want to be redirected by using multiple linked folders.
Last but not least, the same steps described above will apply to older versions of System Center Data Protection Manager (2016, 2012 R2, and 2012).
That’s it there you have it!
Do you want to learn more about System Center Data Protection Manager and how to create a hybrid-cloud backup solution? Make sure to check my recently published book: Microsoft System Center Data Protection Manager Cookbook.
With this book (over 450 pages) on your side, you will master the world of backup with System Center Data Protection Manager and Microsoft Azure Backup Server deployment and management by learning tips, tricks, and best practices, especially when it comes to advanced-level tasks.
Thank you for reading my blog.
If you have any questions or feedback, please leave a comment.