Had a customer raise an interesting issue with the DWStagingAndConfig Service Manager Data Warehouse DB munching its disk space for breakfast.
Problem
The disk that held the DWStagingAndConfig DB logs was full.
Cause
The database was being backed up by DPM but it didn’t seem to be truncating the backup when complete. Because of the way the protection groups worked, it was originally changed from Simple to Full so all the SQL elements could be backed up in one group.
With the recovery model on the DB (under DB properties in SQL mangler) being set to Full, it meant that the size was doubling its requirements to run the backup. So to backup 4gb, it would need a spare 4gb but on failing, it was doubling so needing 8gb, then 16gb and so on…
This meant the the disk only had 10mb of its allocated 80gb free meaning we couldn’t allocate more space easily.
Resolution
Option 1:
In SQL Mangler (Management Studio), switch the recovery mode on the DWStagingAndConfig DB to Simple rather than full.
ALTER DATABASE DWStagingAndConfig
SET RECOVERY SIMPLE;Then shrink the log files for the DWStagingAndConfig DB.
Option 2:
Switch the recovery mode on the DWStagingAndConfig DB to Simple rather than full.
ALTER DATABASE DWStagingAndConfig
SET RECOVERY SIMPLE;Shrink the DB.
Switch the recovery mode on the DWStagingAndConfig DB to Full again.
ALTER DATABASE DWStagingAndConfig
SET RECOVERY FULL;Install Windows Server Backup feature role on target SQL node.