SQL Server Agent hung when SQL Backup GUI is open

evaros69evaros69 Posts: 3
edited March 19, 2012 5:09AM in SQL Backup Previous Versions
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
· Share on Twitter

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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?
    · Share on Twitter
  • evaros69evaros69 Posts: 3
    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
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
    · Share on Twitter
Sign In or Register to comment.