- Remove From My Forums
-
Question
-
I have a maintenance plan which does the Integrity Check task of all databases and Maintenance cleanup task. This job runs with error :
Retries Attempted 0
Message
Executed as user (???). Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 11:17:10 PM Progress: 2019-04-22 23:17:10.85
Source: {D085CB82-FA7F-4BB5-903E-3C9B4EEF3C90} Executing query «DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp…».: 100% complete End Progress DTExec: The package execution
returned DTSER_FAILURE (1). Started: 11:17:10 PM Finished: 11:17:11 PM Elapsed: 0.608 seconds. The package execution failed. The step failed.Any advices how to fix this issue?
-
Edited by
Monday, April 22, 2019 5:55 PM
-
Edited by
Answers
-
Thank you for the input and advices to solve this issues. Yes, it is an old SQL server and service pack need to be updated as well as upgraded.
-
Marked as answer by
Avyayah
Wednesday, May 1, 2019 2:17 PM
-
Marked as answer by
SQL Server agent jobs or SQL Server jobs are predefined tasks performed in SQL Server at scheduled time. Job step may contain T-SQL code, SSIS package, SQL CMD or powershell scripts. Basic idea to get detailed information and output about job step execution either it is failed or successful. A unplanned change in permissions or job related objects may cause a job to be failed. It is important that DBA should have a clear idea about how to get information and troubleshoot when job gets failed. This post is for beginner level DBAs.
Create a Test job
Here we are going to create a test job so that you may play with it and follow in the post as well. Following is the code to create a simple job TestJob which has only one step (TestStep) with erroneous T-SQL code in it.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
USE [msdb] GO /****** Object: Job [TestJob] ******/ EXEC msdb.dbo.sp_delete_job @job_name=N‘TestJob’, @delete_unused_schedule=1 GO /****** Object: Job [TestJob] ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object: JobCategory [[Uncategorized (Local)]] ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N‘[Uncategorized (Local)]’ AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N‘JOB’, @type=N‘LOCAL’, @name=N‘[Uncategorized (Local)]’ IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N‘TestJob’, @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N‘No description available.’, @category_name=N‘[Uncategorized (Local)]’, @owner_login_name=N‘sa’, @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [TestStep] Script Date: 8/10/2018 7:56:12 PM ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N‘TestStep’, @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N‘TSQL’, @command=N‘select ‘‘OK’, @database_name=N‘master’, @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N‘(local)’ IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO |
Failure in a job is granular to step level. It is quite often that any one of job step fails while other may succeed and job over all can be set to FAIL or SUCCEED against any step failure. We have three options to get information about job failure issue. These options may be configured in advanced portion of individual job step. To access it
- Go to job
- Click on steps in left tab
- Click on required step in right pane
- Click on advance in job step frame
At this frame we can see and configure the options related to capturing output related to success or failure of a scheduled job step.
By default configured
Before going to elaborate each of these options, let me describe a by default option to view job step failure message. It is getting job step failure information through job history. No additional configuration is required to retrieve error message through it.
In our created job, I have deliberately left an error in job step so that it may fail when executed. So let us manually execute the job by right clicking on it. It would fail with error.
At this stage we can go to job execution history to view the failure error message for a particular step.
Job history may have data for number of executions. Select and expand the execution that you are interested in. It will show the failure message for the step in lower panel.
You can notice that we did not perform any configurations to get this message but it is there by default. This by default method has a major short comings that tend us to configure and utilize other methods as well. If message section is prolong then it will not be displayed completely in step history pane. Such long messages may come from SSIS package output or any other way.
Three options to get step outcome
The problem with default option of information will be cured by using options explained in next parts of post. In coming sections we will be discussing details of these three options to get job step outcome details.
Include step output in history
Basically this option is not related to job step failure output but for retrieving the execution related output of successful job step. If not enabled even then it will have no effect on step failure output. Enabling it will add the out put of successful step to job step history as well.
Log to table
Logging out put to table will provide enough length to hold large data. There is option to append data with previous execution record. You cannot access this data from job history. Either go to advanced tab of job step and click on view button or select data through query
Command for viewing the log saved in table will be as following where you will have to provide job and step name
|
SELECT j.name AS jobName ,s.step_name AS stepname ,l.log AS LogStatement ,l.date_created ,l.date_modified FROM msdb.dbo.sysjobstepslogs l INNER JOIN msdb.dbo.sysjobsteps s ON l.step_uid = s.step_uid INNER JOIN msdb.dbo.sysjobs j ON j.job_id = s.job_id WHERE j.name = ‘TestJob’ AND s.step_name = ‘TestStep’ |
This option will provide you 2GB size limit to store the job output. But keep in mind that this data will be stored in msdb and may cause to grow msdb unexpected.
Output file
This is my favorite and widely used by DBAs without fear of filling out msdb space or loosing the output from step. In this case step output is configured to flat file on server disk. You will have to provide path pointing to a suitable location on server. Recommender to configure a path other than data, log and tempDB drives so that data files performance is not affected. Also you can set to append to existing output in file to maintain a history for prolong time. File size can grow beyond 2 GB.
What next
Hopefully you have now basic skill to get the information about job step execution. Next you can explore to configure the job failure alert and how to increase the number of records in history of job so that it may not be purged before you expect.
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:
- Run SQL Server Enterprise Manager.
- Expand a server group; then expand a server.
- Expand Management; then expand SQL Server Agent.
- 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:
- Run SQL Server Enterprise Manager.
- Expand a server group; then expand a server.
- Expand Management; then expand SQL Server Agent.
- Double-click the appropriate job and choose the Schedules tab.
- 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:
- Run SQL Server Enterprise Manager.
- Expand a server group; then expand a server.
- Expand Management; then expand SQL Server Agent.
- 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
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.