SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Backup Blocking
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shiyam198
Yak Posting Veteran

94 Posts

Posted - 05/01/2013 :  12:06:08  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1677 Posts

Posted - 05/10/2013 :  17:12:43  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2043 Posts

Posted - 05/11/2013 :  01:54:14  Show Profile  Visit jackv's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000