How To Find Long Running Backups in SQL Server

I’ve written before about how much I love looking for outliers. One example of this can be found in the post I did on the sysjobhistory table. Recently I was asked to put together some code to find out which backups were “running long”. Of course my first question was trying to define what was meant by “long”. That’s when I decided to simply define “long” as any time that is more than two standard deviations away from the mean.

You can use the following code to help get you those details. I used a CTE, but feel free to modify it however you want. I also included a parameter for the minimum number of seconds you would want included in your evaluation. For example, you may not care about backups that run in two seconds, have a standard deviation of one-half a second, and suddenly one day it takes five seconds. That choice is yours, really, so that is why I included the parameter.

Also worth noting is that the backupset table has a column for backup size. In theory you could modify this code to focus on the size of the backup as opposed to the length of time it takes to run. I didn’t go down that route because I was asked to focus on time, not size (and I have always been told that size doesn’t matter anyway).

I think the most important lesson here is that you could use code like this to find other outliers. Anything that has an average that can be calculated, really. I’m thinking about things like query run times, of course…or even the amount of logical I/O for a query. Yep, just about anything.

 

declare @MinAvgSecsDuration int = 2
;
WITH BackupHistData AS
(
  SELECT database_guid, type, MAX(backup_set_id) AS [MAX_BSID]
        ,AVG(CAST(DATEDIFF(s, backup_start_date, backup_finish_date) AS int))
                AS [AVG]
        ,STDEVP(CAST(DATEDIFF(s, backup_start_date, backup_finish_date) AS int))
                AS [SIGMA]
        FROM msdb.dbo.backupset
        GROUP BY database_guid, type
)
SELECT bup.database_name, bup.backup_set_id, bup.type
        ,CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int)
                AS [backup_time_sec]
        ,bhd.[AVG] as [avg_sec]
        ,(1.0*bhd.[AVG]+2.0*bhd.SIGMA) as [max_duration_sec]
FROM BackupHistData bhd
INNER JOIN msdb.dbo.backupset bup ON bhd.database_guid = bup.database_guid
/*Filter for the outliers*/
WHERE CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int) >
                        (1.0*bhd.[AVG]+2.0*bhd.SIGMA)
/*Filter for only the most recent backup, if desired*/
AND bup.backup_set_id = bhd.MAX_BSID
/*Filter for backups with an average duration time, if desired*/
AND bhd.[AVG] >= @MinAvgSecsDuration
/*Filter for specific backup types, if desired*/
AND bhd.type IN ('D', 'I', 'L')

 
DISCLAIMER: Do not run code you find on the internet in your production environment without testing it first. Do not use this code if your vision becomes blurred. Seek medical attention if this code runs longer than four hours. On rare occasions this code has been known to cause one or more of the following: nausea, headaches, high blood pressure, popcorn cravings, and the impulse to reformat tabs into spaces. If this code causes your servers to smoke, seek shelter. Do not taunt this code. 

Good luck.