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.
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.
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?