SQL Server 2005 Jobs

Thu Dec 14, 2006 by Tara Kizer in sql-server-database-administration

We use MOM 2005 to monitor our production environment. We hadn't been receiving any alerts from MOM when a SQL Server 2005 job failed, so I was tasked with tracking the problem down.

I checked the MOM configuration to ensure everything was setup properly. I compared the SQL Server 2005 rules to the SQL Server 2000 rules since we were getting alerts when a SQL Server 2000 job failed. The rules were configured the same.

I then checked the Application Log for the 2005 job failures since that is what MOM uses. I noticed that when a 2005 job failed, it wasn’t reported in the Application Log.

In SQL Server 2000, by default, the “Write to Windows application event log” “When the job fails” option is checked in the "Notifications" tab when you create a job in Enterprise Manager.

In SQL Server 2005, this option is not checked by default.

I modified one of our jobs, that I knew would fail, so that this option was checked. I then ran the job. MOM created an alert for the failed job.

We just took it for granted that this option was checked for us already in SQL Server 2005. If you use MOM or any other software package that relies on the Event Log to find SQL Server 2005 issues, you will need to modify your jobs so that this option is checked.

Since I'm a lazy and forgetful DBA, I am going to setup a job that will run either daily or weekly on each of our production SQL Servers that runs the code below so that the option is checked for all jobs.

Even though this is not a bug in SQL Server 2005, I consider this a serious problem for those of us using products such as MOM to monitor production.

Here is the code to "fix" the existing jobs:

SET NOCOUNT ON

SELECT IDENTITY(int, 1, 1) AS agentJobId, name AS agentJobName
INTO #agentJob
FROM msdb.dbo.sysjobs
ORDER BY name

DECLARE @agentJobName sysname, @agentJobId int, @job_id uniqueidentifier

SET @agentJobId = 1

SELECT @agentJobName = agentJobName
FROM #agentJob
WHERE agentJobId = @agentJobId

WHILE @@ROWCOUNT <> 0
BEGIN
EXEC msdb.dbo.sp_verify_job_identifiers '@job_name', '@job_id', @agentJobName OUTPUT, @job_id OUTPUT

EXEC msdb.dbo.sp_update_job @job_id, @notify_level_eventlog = 2

      SELECT @agentJobId = @agentJobId + 1, @job_id = NULL

      SELECT @agentJobName = agentJobName
      FROM #agentJob
      WHERE agentJobId = @agentJobId
END

DROP TABLE #agentJob