Troubleshooting SQL Server Jobs

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.

»


See All Articles by Columnist
Alexander Chigrik

Alexander Chigrik
Alexander Chigrik
I am the owner of MSSQLCity.Com - a site dedicated to providing useful information for IT professionals using Microsoft SQL Server. This site contains SQL Server Articles, FAQs, Scripts, Tips and Test Exams.

Latest Articles