Red Gate forums :: View topic - SQL Server Agent hung when SQL Backup GUI is open
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 6
SQL Backup 6 forum

SQL Server Agent hung when SQL Backup GUI is open

Search in SQL Backup 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
evaros69



Joined: 23 Feb 2012
Posts: 2

PostPosted: Thu Feb 23, 2012 10:15 pm    Post subject: SQL Server Agent hung when SQL Backup GUI is open Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6643

PostPosted: Mon Feb 27, 2012 6:48 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
evaros69



Joined: 23 Feb 2012
Posts: 2

PostPosted: Tue Feb 28, 2012 11:26 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6643

PostPosted: Wed Feb 29, 2012 11:28 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6643

PostPosted: Tue Mar 06, 2012 11:41 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6643

PostPosted: Thu Mar 08, 2012 11:42 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6643

PostPosted: Mon Mar 19, 2012 10:09 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group