In a previous article (see article) I provided simple queries to return the server’s job history. It’s only natural that when you find yourself running the same script over and over again eventually you’ll get around to turning it into a stored procedure.
Well today was the day.
You can use the script below to create the Stored Procedure usp_GetJobHistory.
To deploy the SP to your instance you’ll need to replace the text PLACEHOLDER with the name of the database you’ll be deploying to. Ideally you should have a utility or administration database rather than deploying to Master.
The SP has 5 parameters.
|@DaysToPull||INT||Any Number||How many days of history you want
working back from today
|@Status||VARCHAR||One of the following: All’, ‘Succeeded’, ‘Failed’,
‘Retried’, ‘Cancelled’, NULL
|Will limited the rows returned
based on the status of the entry
|@SortOrder||VARCHAR||ASC or DESC||The occurrence order i.e. oldest to newest|
|@JobName||VARCHAR||Any Text||Used in a LIKE operator to find jobs containing that text|
|@ReturnSql||BIT||0, 1 or NULL||Returns the SQL you can modify or
run to return the history
Create the SQL code to return the job history for jobs containing the work backup that failed in the past day sorted by newest to oldest.
EXEC dbo.GetJobHistory @DaysToPull = 1
,@SortOrder = ‘DESC’
,@Status = ‘Failed’
,@JobName = ‘Backup’
,@ReturnSql = 1
Tip: you could use this to create a SSRS report that could be published daily to notify stakeholders of any job failures.