SQL Server Agent hangs and becomes unresponsive

From: Sean Blaes (Blaes_at_discussions.microsoft.com)
Date: 12/27/04 

Date: Sun, 26 Dec 2004 16:03:01 -0800

I have my SQL Server and Agent both installed as a power use named
"SQL_User", the jobs include some standard backup and maintenance plan jobs,
along with a fairly long running (up to about 30 minutes) DTS job that
manages downloading and importing data from a remote server via FTP, then
merging that data with my own.

This has been running fairly well for about a year, but lately I've been
having significant problems with SQL Server Agent. It seems to hang
regularly, the symptoms being that if I open Enterprise Manger and view jobs
(after refreshing the view) then all jobs show a last date run of "cannot
determine the last job was run" or something like that. So, I shut down SQL
Server Agent via Enterprise manager, which gives me a message that says it is
not responding to the control request. Enterprise Manager at this point
shows SQL Server Agent as still running. Finally, if I try to shut it down
again via Enterprise Manager, it shuts down immediately, and I can restart
it. I've checked the event viewer and there don't appear to be any error
messages in the system or application logs. I am running SQL Server 2000
SP3. The database is fairly large, but not huge (a few tables with around
1-2 million rows, with many tables on the order of tens of thousands.)

Relevant Pages