System Center Data Protection Manager 2012 certainly is a great tool to manage backups in a SQL Server environment, but recently I have seen a situation that needs some attention of those using it.
When you configure protection for a database DPM identifies the disk where the log file is placed and creates this folder/path:
This is not a problem, per se, but imagine a situation where there is an anormal amount of operations in your database and its log file gets quickly “filled”.
Now imagine that you have not managed this situation right after it happened, so for this time you can count only on your incremental (log) backups made by DPM, that allow the VLFs on your log file to be reused.
The problem in this situation is that when DPM makes its incremental (log) backups it generates a .log file on the path mentioned earlier on this article, and its size will be proportional to the amount of information in the log file.
So at the moment your log file has the biggest amount of information, maybe it is even auto-growing and using more space on your disk, you will need more space at this same disk to make your backups.
What could happen is that in a situation where you have low disk space on the log file disk, when you need the incremental backups the most so you can free some space on the log file and avoid it from growing, this could be the exact moment where you won’t be able to make your backups for the lack of free space, what puts you in a bad situation.
Obviously this could be avoided with a well planned incremental backup frequency, and with disks for log files with enough space, but we know things not always happen the way they should.
And what if you see yourself in this scenario, what can be done? You have a few options.
One of them is to add a new log file in another disk and then deal with the situation more calmly (in fact I have never tried this solution, but I GUESS DPM would create an identical path on the other disk and would allow you to make backups).
Another option, not a really good one in my opinion, would be to make an incremental (log) backup directly using T-SQL on SSMS sending the backup to the NUL device, like this:
BACKUP LOG DATABASE TO DISK = ‘NUL’
Where NUL is similar to the /dev/null device on linux (I have written about this in a previous post – this one in portuguese).
By doing this you would finally have some free space in your log file, but you would lose all of its content, breaking the chain of recovery points in DPM.
In the case you decide to use this “solution” it is highly recomended that you make a FULL backup right after doing so.
Independently of the solution adopted, the best thing to do is to control the frequency of your incremental backups on DPM and keep the disks with enough free space to allow SQL Server and DPM to work without problems.
Anyway, the alert is done about DPM’s behavior so more people would be aware of it and don’t go through this type of situation.