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.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Backup Blocking

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
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-05-11 : 01:54:14
If the first backup is successfull - why would it start again ? Is it possible there is a schedule involved?
As well as suggestions made already - check the backup history of the database - use this script http://www.sqlserver-dba.com/2012/06/display-sql-backup-history-for-a-single-database.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
   

- Advertisement -