Snapshot Backups Occurring

sp_Blitz® Result: Snapshot Backups Occurring

Volume snapshot backups (aka VSS) done by your SAN or VM software can take backups of huge databases in a matter of seconds – when they’re configured correctly. Configured incorrectly, they can freeze your database’s IO for seconds or minutes at a time.

This part of our SQL Server sp_Blitz® script looks at msdb.dbo.backupset for full backups over 50GB that complete in under 60 seconds.

In theory, that sounds fast. In practice, this may be freezing your IO during the duration of the backup.

To get more information about the number of backups, run this query:

SELECT bs.database_name, 
  DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) AS duration_seconds, 
  bs.backup_start_date, bs.backup_finish_date, bs.backup_size
FROM msdb.dbo.backupset bs
WHERE bs.type = 'D'
AND bs.compressed_backup_size >= 20000000000 /* At least 50GB */
AND DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) <= 60 /* Backup took less than 60 seconds */
AND bs.backup_finish_date >= DATEADD(DAY, -14, GETDATE()) /* In the last 2 weeks */
ORDER BY bs.backup_finish_date DESC;

SELECT bs.database_name,

DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) AS duration_seconds,

bs.backup_start_date, bs.backup_finish_date, bs.backup_size

FROM msdb.dbo.backupset bs

WHERE bs.type = 'D'

AND bs.compressed_backup_size >= 20000000000 /* At least 50GB */

AND DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) <= 60 /* Backup took less than 60 seconds */

AND bs.backup_finish_date >= DATEADD(DAY, -14, GETDATE()) /* In the last 2 weeks */

ORDER BY bs.backup_finish_date DESC;

TO FIX THE PROBLEM

Find out who’s doing the snapshot backups – sometimes it’s a well-intentioned SAN or VM admin.

Find out if it’s happening at days/times that are inconvenient to your users, and move ’em to lower-load hours.

Finally, make sure they’re not breaking your LSN chain.

Return to sp_Blitz or Ask Us Questions