Do you have problems with SQL Server jobs?

December 10, 2003
Troubleshooting SQL Server Jobs
By Alexander Chigrik

If you have problems with SQL Server jobs, review this troubleshooting checklist to find potential solutions.

1. Check that you use the latest SQL Server service pack.

Because many bugs were fixed in SQL Server service packs, you should install the latest SQL Server service pack. To check what SQL Server service pack are you running, see this link: How can I check what SQL service pack am I running?

2. Check that Task Scheduler service is running.

This service must be started, if you need the job be scheduled. To start Task Scheduler service on Windows NT choose: Start -> Settings -> Control panel -> Services (choose Startup "Automatic" type) and Start.

To start Task Scheduler service on Windows 2000 choose: Start -> Settings -> Control panel -> Administrative Tools -> Services (choose Startup "Automatic" type) and Start.

3. Check that the SQLServerAgent and EventLog services are running, if all the jobs are not starting.

These services must be started, if you need the job be started. So, if these services are not running, you should run them.

4. Check that the account the SQLServerAgent services runs under is a member of the Domain Users group.

The LocalSystem account does not have network access rights, so if your jobs require resources across the network, or if you want to notify operators through e-mail or pagers, you must set the account the SQLServerAgent service runs under to be a member of the Domain Users group.

5. Check that the job is enabled, if the job is not starting.

The job can be enabled or disabled. To check that the job is enabled, you can do the following:

1. Run SQL Server Enterprise Manager.
2. Expand a server group; then expand a server.
3. Expand Management; then expand SQL Server Agent.
4. Double-click the appropriate job to see that the job enables.

6. If the job works fine when you start it manually, but does not start on the scheduled base, check that the job's schedule is enabled.

The job schedule can be enabled or disabled. To check that the job schedule is enabled, you can do the following:

1. Run SQL Server Enterprise Manager.
2. Expand a server group; then expand a server.
3. Expand Management; then expand SQL Server Agent.
4. Double-click the appropriate job and choose the Schedules tab.
5. Double-click the appropriate job schedule to see that the job schedule is enable.

7. Check the history values of the job to determine the last date the job worked.

To view the history values of the job, you can do the following:

1. Run SQL Server Enterprise Manager.
2. Expand a server group; then expand a server.
3. Expand Management; then expand SQL Server Agent.
4. Right-click the appropriate job and choose 'View Job History'.

8. Check the SQL Server error log, SQL Server Agent error log, and Windows NT or Windows 2000 application log to get a more detailed error description.

Comparing the dates and times for alert failure events between the SQL Server error log, the SQL Server Agent error log, and the Windows NT or Windows 2000 application log can help you to determine the reason of the failure.

9. If the job works, but the responsible operator does not receive notification, try to send an 'e-mail', 'pager', or 'net send' message to this operator manually.

In most cases, this problem arises because you have entered an incorrect 'e-mail', 'pager', or 'net send' addresses. If you can send an 'e-mail', 'pager', or 'net send' message manually to this operator, check the account the SQL Server Agent runs under and check the operator's on-duty schedule.

10. You may get the 8198 error message if a job is owned by a Windows NT authenticated user.

This is a Windows NT 4.0 bug. To work around this problem, change the job owner to a standard SQL Server user or system administrator (sa) or upgrade to Windows 2000.

11. You may get an error in Enterprise Manager if you add a new step to a job that was created as part of building a database maintenance plan.

This is an SQL Server 7.0 bug; SQL Server 2000 does not contain such problems. To work around this problem, do not modify jobs created by the Database Maintenance Plan Wizard.

12. The job step fails if the first INSERT of batch job encounters the error 3604.

If first INSERT of batch job on a table that has a unique index with the IGNORE_DUP_KEY clause violates the uniqueness property of the index, the job step fails with the error 3604. This is SQL Server 2000 bug. This bug was fixed in SQL Server 2000 service pack 1.

13. Multiple SQL Agent jobs owned by the same login and launched at the same time may fail with the primary key error message.

This is SQL Server 2000 bug. This bug was fixed in SQL Server 2000 service pack 1.

14. SQLAgent T-SQL job that contains SET NOCOUNT ON and sends the results to an output file may still write the rowcount line to an output file.

To work around this problem, you should change the job step type to Operating System Command (CmdExec) and use the Osql.exe utility to send the results to an output file.