SQL Server Agent hung when SQL Backup GUI is open

[evaros69]

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

[Brian Donahue]

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
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521

[evaros69]

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

[Brian Donahue]

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
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521

[Brian Donahue]

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
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521

[Brian Donahue]

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
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521

[Brian Donahue]

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
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521