SQL Server Agent hung when SQL Backup GUI is open

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

SQL Server Agent hung when SQL Backup GUI is open

Postby evaros69 » Thu Feb 23, 2012 10:15 pm

Hi,

At our company, we support a SQL Cluster that holds the SSRS databases, among other user dbs.

I'm not an SSRS expert, so excuse me if I'm miss something, but the fact is that SQL Server Agent has thousands of SSRS jobs, above 24K. That is enormous, I know.

Our particular issue is that when someone opens the SQL Backup GUI and connects to the cluster, that session blocks the msdb database and makes SQL Agent to hang; I assume because it's trying to query all the jobs. We need then to kill that session to get the SQL Agent going again, otherwise the reports start failing, the jobs doesn't start and everything related with SQL Agent fails.

Is there a way to avoid SQL Backup to block msdb? Has anyone been into this situation before?

Any comments will be highly appreciated.

Thank you!
Javier
evaros69
 
Posts: 2
Joined: Thu Feb 23, 2012 9:48 pm

Postby Brian Donahue » Mon Feb 27, 2012 6:48 pm

Hello Javier,

Unfortunately, no. I haven't heard of SQL Backup stopping SQL Server Agent. I'm not sure this is even possible as if there is contention, SQL Server is supposed to throw a deadlock and choose a deadlock victim. SQL Server should not be waiting for a SPID to complete, ever.

Have you checked for deadlocks on the msdb database?
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am

Postby evaros69 » Tue Feb 28, 2012 11:26 pm

Thanks for your comment, Brian.

I just to clarify that SQL Agent is not stopped, but hung. The session that comes from SQL Backup block every other processes in msdb and prevent the reports to be created. No deadlocks were detecetd during this situation.
To remediate, we simply kill the spid and everything goes back to normal.

Thank you.

Javier.
Javier
evaros69
 
Posts: 2
Joined: Thu Feb 23, 2012 9:48 pm

Postby Brian Donahue » Wed Feb 29, 2012 11:28 am

Kill which SPID? The xp_sqlbackup one? Because that cannot be killed or rolled back - SQL Server doesn't allow you to kill a SPID running an extended stored procedure like backup has.
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am

Postby Brian Donahue » Tue Mar 06, 2012 11:41 am

Hello,

You may also want to check the SQL Server's error log - there could be a shortage of resources preventing the process from executing. If it's a contention issue, though, I would expect SQL Server to kill one of the processes for you.
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am

Postby Brian Donahue » Thu Mar 08, 2012 11:42 am

We still believe there is a problem with your SQL Server, such as a resource issue. If you can use SQL Profiler to trace the query of the SPID that you kill, maybe we can work from there.
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am

Postby Brian Donahue » Mon Mar 19, 2012 10:09 am

My point exactly - a deadlock, by definition, is going to choose a victim and kill the process, if the SQL Server's scheduler is working properly.
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Backup 6

Who is online

Users browsing this forum: No registered users and 0 guests