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)) ,A.type 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] ,r.Database_Name ,[Backup Type] = r.type ,r.DaysSinceLastBackup ,r.LastBackupDate ,r.BackupSize_GB ,r.BackupSize_MB ,F.physical_device_name ,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
is it maybe something else running at the same time? like backups to take or other types of backup (non-sql server)