
	About Us
	SSWUG Articles
	Articles

	Administering
	Comparison
	General
	Know How
	Replication
	Tuning
	Undocumented
	UDF
	SQL 6.5

	FAQ
	Scripts
	Tips
	Test Exams
	Advertise
	Download
	History
	Search
	Traffic
	Related Links


Add to Favorites

Troubleshooting SQL Server Jobs

Alexander Chigrik
chigrik@mssqlcity.com

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 the Windows NT choose:
Start -> Settings -> Control panel -> Services
(choose Startup "Automatic" type) and Start.
To start Task Scheduler service on the 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, it these services are not running, you should run them.

*****
4. Check that 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 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 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 appropriate job to see that job enables.

*****
6. If the job worked 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 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 appropriate job and choose the Schedules tab.
5. Double-click appropriate job schedule to see that job schedule is enable.

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

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 appropriate job and choose 'View Job History'.

*****
8. Check SQL Server error log, SQL Server Agent error log, and Windows NT or Windows 2000 application log to get 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 'e-mail', 'pager', or 'net send' message to this operator manually.

In most cases, this problem was arisen because you have entered incorrect 'e-mail', 'pager', or 'net send' addresses. If you can send '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 can get 8198 error message, if a job owned by a Windows NT authenticated user.

This is 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 can get the 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 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 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 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 Osql.exe utility to send the results to an output file.

*****

Visit The SQL Server Worldwide User's Group for all the latest news and information about SQL Server, Oracle, DB2 and XML for developers and administrators.

(c) 1997, 2005 Bits on the Wire, Inc