SQL Server backups started to fail now and then due to space issues. There is something that I can do to make it work?
The answer to that question is obvious and we just need to allocate more space to the location where we save the backups. But how much space we need to allocate? The answer is to check the space used by the last full backup for all the databases as a starting point and multiply it with the retention period.
In order to have that value we can use the below script to see the sum of all the latest full backups taken on our instance. Because of the reasons below the size that we see might not be the total size that we need but it is still a good estimation of what we might need for a successfull backup.
- databases were created and for them we haven’t taken yet a backup
- databases were backed up in the past but the file is not available on the disk to check the size
The script give us a real number when the last full backup of all the databases was successfully and the files are still on disk.
In our environment that full backup file receives _full while in other environments the naming convention might be different which is why the script must be modified a little bit. Also if we want to have an estimation about latest differential we need to modify the script. In red below you can see where you need to modify for the desired results.
SELECT bmf.physical_device_name as path FROM
(select media_set_id = MAX(bs.media_set_id) FROM sys.databases d LEFT OUTER JOIN msdb.dbo.backupset bs ON bs.database_name = d.name AND bs.is_copy_only = 0 AND bs.type =’D‘ GROUP BY d.Name, bs.type) r
LEFT OUTER JOIN msdb.dbo.backupmediafamily bmf ON r.media_set_id = bmf.media_set_id where bmf.physical_device_name is not null and bmf.physical_device_name like ‘%_full%’
The script output looks like below
and you can download it from here