SQL Backup starting taking 4x as long to run.. Just starting happening overnight..

Question

SQL Backup starting taking 4x as long to run.. Just starting happening overnight..

itpcpro

Ten Centuries

Points: 1043
More actions
December 22, 2010 at 12:12 pm

#231002

I have two production databases that both run there backups to a SAN drive. About a week ago the backup on one of the servers went from taking 4 hrs to 12 hrs. The size of the database is over 800 GB. The other went from hour and a half to 4 hrs. The size is this database is over 600 GB. This all happened over night.. one day fine the next day very slow...
My question is: Is there any monitoring tool I can use to track to see where the slowness is happening? Backup.. Network.. SAN...
I checked the Activity Monitor and there were no block but the ASYNC_IO_COMPLETION was around 5 million or so wait time. Which I have read this could cause disk bottlenecks...
I contacted our Network Admin and he said that the SAN was running normal and had no problems.
Also I ran this query while the Backups were running but it only gave me the time remaining.
SELECT A.NAME,B.TOTAL_ELAPSED_TIME/60000 AS [Running Time],
B.ESTIMATED_COMPLETION_TIME/60000 AS [Remaining],
B.PERCENT_COMPLETE as [%],(SELECT TEXT FROM sys.dm_exec_sql_text(B.SQL_HANDLE))AS COMMAND FROM
MASTER..SYSDATABASES A, sys.dm_exec_requests B
WHERE A.DBID=B.DATABASE_ID AND B.COMMAND LIKE '%BACKUP%'
order by percent_complete desc,B.TOTAL_ELAPSED_TIME/60000 desc
Thanks
Scott

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

dan-572483 SSCoach Points: 15870 More actions · Answer 1

We have a similar setup and encountered a similar situation. What happened was I scheduled a backup of a 200GB DB during a time window when there were no other jobs on that server. The backups took 3-4 times longer than manually launched test backups during the day. A backup job on another server began running 2-3 time longer than normal and users of a 3rd system complained of slowness during that time window.

It turned out that the three systems were all using the same LUN on the SAN. While SANs are designed high performance with different systems using them similtaniously, there is a limit, and the adding the new backup exceeded it. I rescheduled the new backup for a different time and everything began running in reasonable timeframes.

Find out from your SAN admin what systems are on the same LUN and schedule the big jobs on these systems so they don't overlap.

itpcpro Ten Centuries Points: 1043 More actions · Answer 2

Thanks Dan...

I am going to try alternating the backups tonight were they are not running at the same time and maybe that will improve the performance. I am sure they are on the same SAN so hopefully this will clear up the problem.

willtwc Ten Centuries Points: 1099 More actions · Answer 3

When was the SAN last reboot? The SAN may need a reboot to clear the cache.

itpcpro Ten Centuries Points: 1043 More actions · Answer 4

Well I rescheduled the backups to run at different time were they would not overlap and they are running like an hour less now but still taking 8 hours were it was only taking 3 hours before.

Still looking for any suggestions...

Ray Mond SSCertifiable Points: 7159 More actions · Answer 5

Try the suggestions in this article and its sub-articles. Provides some tests you could run.

Thanks.

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.