Sql Server Monitoring Job History Complete Guide

Step 1: Understand the SQL Server Agent and Job History

SQL Server Agent is a Windows service that runs scheduled jobs. Job history records information about the execution of these jobs.

Step 2: Create a Monitoring Job (Example)

First, let's create a sample SQL Server Agent job named TestSampleJob that does something simple like inserting the current date and time into a table. We'll later check the history of this job.

Example 1: Creating a Table for Testing

CREATE TABLE dbo.TestJobTable (
    ExecutionTime DATETIME NOT NULL DEFAULT GETDATE()
);

Example 2: Creating the Sample Job

-- Connect to MSDB database where SQL Server Agent data resides.
USE msdb;
GO

EXEC sp_add_jobgroup @job_group = N'TestingJobGroup';
GO

EXEC sp_add_job 
    @job_name = N'TestSampleJob', 
    @description = N'A test job to insert current date and time into TestJobTable.', 
    @owner_login_name = N'sa',  -- Use appropriate owner name
    @job_group = N'TestingJobGroup';
GO

-- Add job steps
EXEC sp_add_jobstep 
    @job_name = N'TestSampleJob', 
    @step_name = N'InsertCurrentDateTime', 
    @subsystem = N'TSQL', 
    @command = N'INSERT INTO your_db.dbo.TestJobTable (ExecutionTime) VALUES (GETDATE());';
GO

-- Schedule the job
EXEC sp_add_schedule 
    @schedule_name = N'DailyAtNoon', 
    @freq_type = 4,              -- Daily
    @freq_interval = 1,          -- Every day
    @active_start_time = 120000; -- Noon
GO

-- Attach schedule to job
EXEC sp_attach_schedule 
    @job_name = N'TestSampleJob',
    @schedule_name = N'DailyAtNoon';
GO

-- Add job server associations - we use "(local)" to run the job on the local server
EXEC sp_add_jobserver 
    @job_name = N'TestSampleJob', 
    @server_name = N'(local)';
GO

Step 3: Start the SQL Server Agent Service

Ensure that your SQL Server Agent is running. You can start it from SQL Server Configuration Manager or Services management console.

Step 4: Manually Run the Job and Monitor Its Execution

You can manually execute the job to generate some initial history.

Manually Running the Job

USE msdb;
GO
EXEC sp_start_job @job_name = N'TestSampleJob';
GO

Fetching Job History Information

After running the job, you can query the job history from the msdb.dbo.sysjobhistory table.

Example 3: Querying the Job History

USE msdb;
GO

SELECT 
    j.name AS JobName,
    h.run_date AS RunDate,
    h.run_time AS RunTime,
    h.run_duration AS RunDuration,
    h.message AS Message,
    CASE 
        WHEN h.run_status = 0 THEN 'Failed'
        WHEN h.run_status = 1 THEN 'Succeeded'
        WHEN h.run_status = 2 THEN 'Retry'
        WHEN h.run_status = 3 THEN 'Canceled'
        WHEN h.run_status = 4 THEN 'In Progress'
    END AS RunStatus
FROM 
    dbo.sysjobs j
INNER JOIN 
    dbo.sysjobhistory h ON j.job_id = h.job_id
WHERE 
    j.name = N'TestSampleJob'
ORDER BY 
    h.instance_id DESC;

Step 5: Formatting Date and Time Correctly

The run_date and run_time columns in sysjobhistory are integers in specific formats. Let's format them into more readable forms.

Example 4: Formatting Job Date and Time

USE msdb;
GO

SELECT  
    j.name AS JobName,
    CONVERT(DATETIME, STUFF(STUFF(CAST(h.run_date AS CHAR(8)), 7, 0, '-'), 5, 0, '-') + 
    CONVERT(DATETIME, STUFF(STUFF(REPLACE(STR(h.run_time, 6), ' ', '0'), 5, 0, ':'), 3, 0, ':')) AS DateTimeStarted,
    h.run_duration / 10000 AS DurationHour,
    (h.run_duration % 10000) / 100 AS DurationMinute,
    h.run_duration % 100 AS DurationSecond,
    CASE 
        WHEN h.run_status = 0 THEN 'Failed'
        WHEN h.run_status = 1 THEN 'Succeeded'
        WHEN h.run_status = 2 THEN 'Retry'
        WHEN h.run_status = 3 THEN 'Canceled'
        WHEN h.run_status = 4 THEN 'In Progress'
    END AS RunStatus,
    h.message AS Message
FROM  
    dbo.sysjobs j
INNER JOIN 
    dbo.sysjobhistory h ON j.job_id = h.job_id
WHERE 
    j.name = N'TestSampleJob'
ORDER BY 
    h.instance_id DESC;

Explanation:

• run_date: Stored as YYYYMMDD integer. We convert this integer to a DATETIME by splitting it into parts and adding dashes.

• run_time: Stored as HHMMSS integer. We convert this integer to a DATETIME by splitting it into parts and adding colons.

• run_duration: Duration in SSMMHH format (seconds, minutes, hours). We use modulo (%) and division (/) operations to break it down into separate parts.

Step 6: Create a View for Easy Access (Optional)

For easier access, especially if you plan on monitoring the history frequently, you might want to create a view.

Example 5: Creating a View for Job History

USE msdb;
GO

CREATE VIEW dbo.View_JobHistory
AS
SELECT  
    j.name AS JobName,
    CONVERT(DATETIME, STUFF(STUFF(CAST(h.run_date AS CHAR(8)), 7, 0, '-'), 5, 0, '-') + 
    CONVERT(DATETIME, STUFF(STUFF(REPLACE(STR(h.run_time, 6), ' ', '0'), 5, 0, ':'), 3, 0, ':')) AS DateTimeStarted,
    h.run_duration / 10000 AS DurationHour,
    (h.run_duration % 10000) / 100 AS DurationMinute,
    h.run_duration % 100 AS DurationSecond,
    CASE 
        WHEN h.run_status = 0 THEN 'Failed'
        WHEN h.run_status = 1 THEN 'Succeeded'
        WHEN h.run_status = 2 THEN 'Retry'
        WHEN h.run_status = 3 THEN 'Canceled'
        WHEN h.run_status = 4 THEN 'In Progress'
    END AS RunStatus,
    h.message AS Message
FROM  
    dbo.sysjobs j
INNER JOIN 
    dbo.sysjobhistory h ON j.job_id = h.job_id
ORDER BY 
    h.instance_id DESC;
GO

Now, you can simply query the view without writing the full conversion logic each time:

SELECT * FROM msdb.dbo.View_JobHistory WHERE JobName = 'TestSampleJob';

Step 7: Clean Up (Optional)

Once you're done testing, drop the job, schedule, and group created in Example 2.

Example 6: Dropping the Job, Schedule, and Group

USE msdb;
GO

-- Delete the job from the job server
EXEC sp_delete_jobserver 
    @job_name = N'TestSampleJob', 
    @server_name = N'(local)';

-- Delete the schedule
EXEC sp_delete_schedule 
    @schedule_name = N'DailyAtNoon';

-- Delete the job itself
EXEC sp_delete_job 
    @job_name = N'TestSampleJob';

-- Delete the job group
EXEC sp_delete_jobgroup 
    @job_group = N'TestingJobGroup';
GO

Conclusion

In this example, we walked through creating a SQL Server Agent job, running it manually, querying its history, formatting the relevant details, and optionally creating a view for easy access. Always make sure you have proper permissions to manage SQL Server Agent jobs and query the msdb database.