I've seen situations in which we have some SQL Server Agent jobs that are scheduled to run very rarely (like once every year or once every half year). Either because of audit or troubleshooting, we need to find out those SQL Server Agent jobs last run time and their outcome. However, the SQL Server Agent history is being purged every few months (Notes: purging SQL Server Agent history periodically is not a bad thing, it is actually a good practice to keep the msdb database size manageable, esp. if your environment has lots of SQL Server Agent jobs, in which many of them have lots of job steps, and some of the jobs are scheduled to run frequently - every few minutes or even seconds).
Are we out of luck? Well not necessarily.
Demo
First create a SQL Agent job with the following script:
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
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'SQL Job Test',
@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
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Job Step 1',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@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 1',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Job Step 2',
@step_id=2,
@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 2',
@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
Then we run the job using the following script:
USE [msdb];
GO
EXEC sp_start_job @job_name=N'SQL Job Test';
GO
Once that has been completed, there should be a job history for the SQL Server agent job:
Now purge the job history by running the following:
USE [msdb];
GO
EXEC dbo.sp_purge_jobhistory @job_name=N'SQL Job Test';
GO
After purging the job history, we will no longer have the entries for “SQL Job Test” job on dbo.sysjobhistory table in the msdb database.
If we try to query the sysjobhistory, using the following query, we would get 0 row:
USE [msdb];
GO
SELECT *
FROM dbo.sysjobhistory
WHERE job_id IN (
SELECT job_id
FROM dbo.sysjobs
WHERE [name] = N'SQL Job Test');
Or this query:
USE [msdb];
GO
EXEC sp_help_jobhistory @job_name = N'SQL Job Test';
GO
Gulp! No more history for SQL job “SQL Job Test”. But wait, if we look at the job properties, we can see when the job was last executed.
So the information must still be in the database. If we try to run the following query:
USE [msdb];
GO
EXEC sp_help_job @job_name = N'SQL Job Test';
We can see the job’s last run date, time and even outcome (For the definition of last_run_outcome column, you can check the following MSDN article). There’s more. When you run the sp_help_job stored procedure, you can also see each of the job step’s last run date, time and outcome.
If we investigate it further, the last run date, time and outcome are stored on sysjobservers and sysjobsteps within the msdb database. We can query them using the following:
USE [msdb];
GO
SELECT j.job_id,
j.Name,
js.last_run_outcome,
last_run_outcome_desc = CASE
WHEN js.last_run_outcome = 0
THEN 'Failed'
WHEN js.last_run_outcome = 1
THEN 'Succeeded'
WHEN js.last_run_outcome = 3
THEN 'Canceled'
ELSE 'Unknown'
END,
js.last_outcome_message,
last_run_datetime = dbo.agent_datetime(
CASE WHEN js.last_run_date = 0 THEN NULL ELSE js.last_run_date END,
CASE WHEN js.last_run_time = 0 THEN NULL ELSE js.last_run_time END),
js.last_run_duration
FROM dbo.sysjobs j
INNER JOIN dbo.sysjobservers js
ON j.job_id = js.job_id
Or if you want to see it for each of the job’s steps:
USE [msdb];
GO
SELECT j.job_id,
j.Name,
js.last_run_outcome,
last_run_outcome_desc = CASE
WHEN js.last_run_outcome = 0
THEN 'Failed'
WHEN js.last_run_outcome = 1
THEN 'Succeeded'
WHEN js.last_run_outcome = 3
THEN 'Canceled'
ELSE 'Unknown'
END,
js.last_outcome_message,
last_run_datetime = dbo.agent_datetime(
CASE WHEN js.last_run_date = 0 THEN NULL ELSE js.last_run_date END,
CASE WHEN js.last_run_time = 0 THEN NULL ELSE js.last_run_time END),
js.last_run_duration,
jst.step_id,
jst.step_name,
step_last_run_outcome = jst.last_run_outcome,
step_last_run_outcome_desc = CASE
WHEN jst.last_run_outcome = 0
THEN 'Failed'
WHEN jst.last_run_outcome = 1
THEN 'Succeeded'
WHEN jst.last_run_outcome = 2
THEN 'Retries'
WHEN jst.last_run_outcome = 3
THEN 'Canceled'
ELSE 'Unknown'
END,
step_last_run_datetime = dbo.agent_datetime(
CASE WHEN jst.last_run_date = 0 THEN NULL ELSE jst.last_run_date END,
CASE WHEN jst.last_run_time = 0 THEN NULL ELSE jst.last_run_time END)
FROM dbo.sysjobs j
INNER JOIN dbo.sysjobservers js
ON j.job_id = js.job_id
INNER JOIN dbo.sysjobsteps jst
ON j.job_id = jst.job_id
WHERE j.name = N'SQL Job Test';
Clean-up
To clean up the demo, you can run the following script:
USE [msdb];
GO
EXEC sp_delete_job @job_name = N'SQL Job Test';
GO
