asked
Backup is taking way too longer
Hello all, I have a userdb which is approx 10GB in size, and is taking nearly 5 hrs to finish the backup. Its a normal SQL backup that i am trying to perform. Until yesterday it'd finish in 15 mins, and now it runs forever. Any idea as to why is this taking so long? When queried sysprocesses against this db it show waittype as ASYNC_IO_COMPLETION and wait time 3757812. Any help would be greatly appreciated. Thank you.
@Pradeep407 - Do you have any updates? If it's been resolved can you share so others can benefit? If it hasn't been then add details for the questions I asked below. We aren't full-time support staff but are willing to lend a hand; and the only cost to you is communication :)
Its a regular backup command Backup database to disk='Path' And the storage is physically attached. I guess something is wrong with the OS, it got exceptionally slow all of a sudden. Informed this to Windows team.
This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
Thank fritchey/Rushton for the quick reply. Backup is going to local drive itself. But not sure what's causing the slowness.
We only know what you told us, and that wait type has two causes, a slow network process, or, SQL Server is waiting for action from a user application. In the case of backups, it's pretty unlikely to be the latter. What is the backup command you're running? Is the local drive actual physically attached storage, or a local SAN? What are the other wait types?
The wait says it's an IO issue, that it's trying to backup across a network. That's frequently a very long process. I'd suggest instead to backup locally and then copy the backup file. While it sounds counter-intuitive, that's faster.
Assuming nothing has changed from the SQL Backup perspective, then it sounds as though something is going wrong with / slowing down your network. Have your network ops guys had a look?
I had a similar issue last week. There was a failed backup onto a tape destination. The software that initiated it didn't close its session and SQL Server continued processing, waiting for an acknowledgement that would never come. And since SQL Server only wants to process backups serially no other backup attempts could be initiated. We tried to kill the process but then it was just stuck in a rollback. Our only way to correct this was by restarting SQL Server.
We need you to help us help you. Please answer the following: Have any security settings been altered recently? Is the actual file getting created? Is the backup being blocked by other processes? Do you have multiple backups running? And are they trying to use the same output file? Can you copy files to the backup location in Windows? Are their errors in the System Event Viewer that may aide in resolving this? Is the SQL Server VSS Writer service running? Is the Volume Shadow Copy service running?
16 People are following this question.
