The following is an email from one of my colleagues about an hour ago:

Any Idea why the backup time has gone up from 2.5 hrs to 5.5 hours since the 16th Sept?

The duration has remained consistent on ourserver\REP- at around 1 hour, and they have the exact same data on them. Has something been added since the 15th that would cause disk conetention?

Any other ideas?

how can I find out where the backups are taking more time to run?

I currently can find which backups are taking longer using the following script:

;with Radhe as (
                    SELECT  @@Servername as [Server_Name],
                    B.name as Database_Name, 
                    ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
                    ISNULL(Convert(char(11), MAX(backup_finish_date), 113)+ ' ' + CONVERT(VARCHAR(8),MAX(backup_finish_date),108), 'NEVER') as LastBackupDate
                    ,BackupSize_GB=CAST(COALESCE(MAX(A.BACKUP_SIZE),0)/1024.00/1024.00/1024.00 AS NUMERIC(18,2))
                    ,BackupSize_MB=CAST(COALESCE(MAX(A.BACKUP_SIZE),0)/1024.00/1024.00 AS NUMERIC(18,2))
                    ,media_set_id = MAX(A.media_set_id)
                    ,[AVG Backup Duration]= AVG(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS int))
                    ,[Longest Backup Duration]= MAX(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS int))
                    FROM sys.databases B 

                    inner JOIN msdb.dbo.backupset A 
                                 ON A.database_name = B.name 
                                AND (A.type = 'D'  )

                    GROUP BY B.Name, A.type


                 SELECT r.[Server_Name]
                       ,[Backup Type] = r.type 
                       ,r.[AVG Backup Duration]
                       ,r.[Longest Backup Duration]

                   FROM Radhe r

                    LEFT OUTER JOIN msdb.dbo.backupmediafamily F
                                 ON R.media_set_id = F.media_set_id

                    ORDER BY [AVG Backup Duration] desc, r.Server_Name, r.Database_Name

enter image description here

is it maybe something else running at the same time? like backups to take or other types of backup (non-sql server)

  • 2
    The first thing I'd do is check for increased disk latency against the backup drive. I'd actually remove Sql Server from the equation and try to copy an existing backup (that was created when things were fast) to the target. How long does that take? If you can kick off another backup, you could monitor for disk latency using this script - sqlskills.com/blogs/paul/capturing-io-latencies-period-time Sep 19 '16 at 11:05
  • 1
    How about cracking open perfmon and running captures while the backups execute? Do you have any existing monitoring going on against the servers in your environment which might already have this information that you could look back to? Unfortunately virtual file stats is only going to give you information about the SQL data/logs, and maybe not your backup drive. Also, look to see if any new databases were created with files on the F drive that you are using for backups.
    – Nic
    Sep 19 '16 at 20:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.