March 20, 2007 at 1:04 pm
I'm trying to configure an alert if a job has gone into a suspended state in SQL Server 2005. I know you can dump this information via sp_help_jobs, but what table houses that the current execution status of a job?
March 20, 2007 at 4:32 pm
Job status available in msdb..sysjobhistory table but I don't think SUSPENDED status exists...
You see the SUSPENDED status in sysprocesses table...
If the job status is in progress find the SPID of the job and check the status in sysprocesses table...
MohammedU
Microsoft SQL Server MVP
March 20, 2007 at 4:49 pm
We had an issue that was kind of fallout from bad time updates from a domain controller that put some backup jobs into a suspended state (suspended = 5). The only way that we could get any backups to run was resync the time with the domain controller (basically bouncing the sql services). We think it is a bug in SQL 2005 SP1. The job went into a rollback state that never ended.
I just need to construct a monitor that cuts a problem ticket on our system if that status is 5, so we can react, because it gives little or no information in any of the SQL logs to catch it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply