I'm using Microsoft System Center Data Protection Manager 2016 to protect SQL Server 2017 on Windows Server 2016. I believe that whenever DPM does a backup (synchronization or express full) it will temporarily use some space on the SQL Server host before the data are transmitted to the DPM server.
I have found some support for this belief, specifically for transaction log backups, in https://blogs.technet.microsoft.com/dpm/2013/08/21/optimizations-in-protecting-sql-databases-with-high-churn-by-dpm/ which says (in part)
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.
(note that elsewhere in the post it becomes clear that the directory name actually starts DPM_SQL_PROTECT
, there's just a typo in the part I quoted).
However, I have not been able to find any similar information about temporary space usage for express full backups (which would be a SQL Server full database backup). Looking at the DPMRA.errlog files on the protected SQL Server suggests that the local VSS shadow copy space is used:
CMultiVolumeUsnIterator:AddIncludeFiles(filepath:e:\Program Files\ Microsoft SQL Server\MSSQL14.SQLNGR1\MSSQL\Data\, filespec:kslds.mdf, snapshotpath:\? \GLOBALROOT\Device \HarddiskVolumeShadowCopy9\Program Files\Microsoft SQL Server\MSSQL14.SQLNGR1\MSSQL\Data)
but I am not sure I am interpreting it correctly. In addition, on that protected server, even though many express full backups have been completed, vssadmin list shadowstorage
reports
No items found that satisfy the query.
which seems to contradict the idea that VSS space is being used.
Finally, SQL Server's own logs have entries corresponding to the express full backups which identify the dump device as
device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{990A7582-1356-4B6B-8D70-8F3235748794}1'})
in which the GUID seems to change each time, but I haven't been able to find any explanation of what real storage is behind the virtual device.
Does anybody know how to determine (and perhaps control) the location and amount of space used on the protected server for these backups?
thanks!
Martin