Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shiyam198
Yak Posting Veteran
94 Posts |
 Posted - 2013-05-01 : 12:06:08
|
| Hi,I started seeing numerous blocks on a SQL server instance and I ran sp_whoisactive.There was a 2TB database's FULL backup job has been running for 15 hours. It started last night 8 pm. I also have a scheduled differential backup on the same database last night 9.30 pm. It was blocked the FULL backup job, this was throwing blocking messages on the SQL server.Here is what happened now. I kept refreshing on sp_whoisactive. the full backup job's "percent complete" went to 100% and starting again with 1 %. this time it seems faster went up to 35% already. I am not sure if this is what that kept happening since last night or if this is the true second time.here are my questions:1. What does it mean if a FULL backup job goes in circles?2. The wait type for this job "(1571681ms)ASYNC_IO_COMPLETION" everytime I refresh. I think this is normal for any backups. Should this worry?3. Now I don't see the differential backup job in the "Sp_whoisactive" results?Is there any way for me to know, if this job really finished successfully or not?Thanks for your help guys. Regards,Shiyam |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2013-05-10 : 17:12:43
|
| Have you checked the Job History? It will tell you when a job started, finished and the status. Do you have retries set for the Full Backup job? If so, this could explain why it is starting over. The bigger issue is why the differential backups are running if the full backup is still active. You can get information about running jobs using sp_help_job and looking for at @execution_ststus. Our differential backup job looks at this first to see if its big brother is active and aborts (successfully) if it is still running.=================================================There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
|
|
|
|
|
Backup Blocking