Tag Archives: Job

How to disable all enabled user job schedules in SQL Server with T-Sql

I’d really recommend not running the output of this script on a live environment! This is just a little something I whipped up to test on a development environment.

Running the below script will output the T-Sql required to disable all enabled job schedules on the SQL Server instance. Just copy the outputted text and paste into a new SSMS window and execute to disable the jobs.

SET NOCOUNT ON

DECLARE @Schedule TABLE (ScheduleId INT)
DECLARE @ScheduleId AS INT
DECLARE @Sql AS VARCHAR(MAX)

SET @ScheduleId = ''

INSERT INTO @Schedule (ScheduleId)
SELECT s.schedule_id
FROM msdb.dbo.sysschedules AS s
WHERE s.[enabled] = 1
	AND s.owner_sid <> 0x01
ORDER BY s.NAME ASC

WHILE @ScheduleId IS NOT NULL
BEGIN
	SET @ScheduleId = (
			SELECT MIN(ScheduleId)
			FROM @Schedule
			WHERE ScheduleId > @ScheduleId
			)
	SET @Sql = '
EXEC msdb.dbo.sp_update_schedule @schedule_id = ' + '''' + CONVERT(VARCHAR(5), @ScheduleId) + '''' + '
	,@enabled = 0; 
'

	PRINT @Sql
END
Advertisements

How to get MS SQL Server job history using a stored procedure

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.

Parameter Datatype Options Definition
@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

Example command:

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.

-- REPLACE PLACEHOLDER
USE [PLACEHOLDER];
GO

IF OBJECT_ID('[usp_GetJobHistory]') IS NULL
	EXEC ('CREATE PROCEDURE dbo.[usp_GetJobHistory] AS SELECT 1')
GO

ALTER PROCEDURE [dbo].[usp_GetJobHistory] @DaysToPull INT
	,@Status VARCHAR(9) = NULL
	,@SortOrder VARCHAR(4) = NULL
	,@JobName VARCHAR(256) = NULL
	,@ReturnSql BIT = NULL
AS
BEGIN
	-- =============================================
	-- Author:      Bloggins
	-- Create date: 20170420
	-- Description: <Query to retrieve job history bypassing SSMS inbuilt viewer>
	-- Website: https://techtidbytes.wordpress.com/
	-- =============================================
	SET NOCOUNT ON

	BEGIN TRY
		-- Variable Declarations 
		DECLARE @PreviousDate DATETIME
		DECLARE @Year VARCHAR(4)
		DECLARE @Month VARCHAR(2)
		DECLARE @MonthPre VARCHAR(2)
		DECLARE @Day VARCHAR(2)
		DECLARE @DayPre VARCHAR(2)
		DECLARE @FinalDate INT
		DECLARE @StatusClause AS VARCHAR(255)
		DECLARE @Sql AS VARCHAR(MAX)

		IF @SortOrder IS NULL
			OR (
				@SortOrder <> 'ASC'
				AND @SortOrder <> 'DESC'
				)
		BEGIN
			SET @SortOrder = 'ASC'
		END

		IF @Status = 'All'
			OR @Status IS NULL
			OR (
				@Status <> 'All'
				AND @Status <> 'Failed'
				AND @Status <> 'Succeeded'
				AND @Status <> 'Retried'
				AND @Status <> 'Cancelled'
				)
		BEGIN
			SET @StatusClause = '0, 1, 2, 3'
		END
		ELSE IF @Status = 'Failed'
		BEGIN
			SET @StatusClause = '0'
		END
		ELSE IF @Status = 'Succeeded'
		BEGIN
			SET @StatusClause = '1'
		END
		ELSE IF @Status = 'Retried'
		BEGIN
			SET @StatusClause = '2'
		END
		ELSE IF @Status = 'Cancelled'
		BEGIN
			SET @StatusClause = '3'
		END

		SET @PreviousDate = DATEADD(dd, - @DaysToPull, GETDATE())
		SET @Year = DATEPART(yyyy, @PreviousDate)

		SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))

		SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)), 2)

		SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))

		SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)), 2)

		SET @FinalDate = CAST(@Year + @Month + @Day AS INT)
		/*RunDuration FORMAT (DD:HH:MM:SS)*/
		SET @Sql = '
SELECT ROW_NUMBER() OVER(ORDER BY h.instance_id ' + @SortOrder + ' ) AS Row
	,h.Server AS ServerName
	,s.database_name AS DbName
	,j.name AS JobName
	,s.step_name AS StepName
	,h.step_id AS StepId
	,CASE 
		WHEN h.run_status = 0
			THEN ''Failed''
		WHEN h.run_status = 1
			THEN ''Succeeded''
		WHEN h.run_status = 2
			THEN ''Retried''
		WHEN h.run_status = 3
			THEN ''Cancelled''
		END AS RunStatus
	,MSDB.DBO.AGENT_DATETIME(h.run_date, h.run_time) AS RunTime
	,STUFF(STUFF(STUFF(RIGHT(REPLICATE(''0'', 8) + CAST(h.run_duration AS VARCHAR(8)), 8), 3, 0, '':''), 6, 0, '':''), 9, 0, '':'') AS RunDuration
	,h.sql_severity AS SqlSeverity
	,h.message AS MessageReturned
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id
	AND h.step_id = s.step_id
WHERE h.run_status IN (' + @StatusClause + ')
	AND h.run_date > ' + CONVERT(VARCHAR(255), @FinalDate)

		IF @JobName IS NOT NULL
		BEGIN
			SET @Sql = @Sql + '
AND j.name LIKE ' + '''' + '%' + @JobName + '%' + '''' + ''
		END

		SET @Sql = @Sql + '
ORDER BY h.instance_id ' + @SortOrder

		IF @ReturnSql = 1
		BEGIN
			PRINT (@Sql)
		END
		ELSE
		BEGIN
			EXEC (@Sql)
		END
	END TRY

	BEGIN CATCH
		PRINT 'error!'

		DECLARE @error_number AS INTEGER
		DECLARE @error_message AS VARCHAR(400)

		SET @error_number = error_number()
		SET @error_message = left(error_message(), 400)

		PRINT 'error_message: ' + @error_message
	END CATCH
END

How to capture command prompt output and write it to text file

Say you have a batch script that you run that throws back information you want to keep. Well maybe the best way to do that is to have the output written to a text file. This technique can then be combined with a task scheduler so you have an  automated task that creates a log file.

The below batch script below has two main parts. The first part creates a file to capture the command output. The second part is the command prompt command to run.

Creating the file takes the following 2 parameters:

  • filePath: Where you want the file written, defaulted to your desktop
  • baseName: What the file will be called exclusive of a time stamp, defaulted to Output

The script will then take these parameters to create the parameter fileName, which combines the directory location, with what you want the file to be called along with a time stamp, e.g. User\Desktop\Output20170213111157

The command prompt command goes between the parenthesis (). The results of this command will be written to the file.

@ECHO off
REM SET FILE OUTPUT DIRECTORY
SET "filePath=%USERPROFILE%\Desktop\"
REM SET THE BASE OF THE NAME FOR THE OUTPUT FILE 
SET "baseName=Output"
REM CREATE TIMESTAMP
SET timeStamp=%date:~6,8%%date:~3,2%%date:~0,2%%time:~0,2%%time:~3,2%%time:~6,2%
REM CREATE VARIABLE TO HOLD FILENAME WITH A TIMESTAMP
SET "fileNameStamp=%baseName%%Timestamp%"
SET "fileName=%filePath%%fileNameStamp%"
REM CAPTURE COMMAND OUTPUT TO TEXT FILE
> %fileName%.txt (
REM This is where the commands go!
ECHO Text Captured
)

The following working example captures your computer network information and writes it to the file NetworkInfo(time stamp) on your desktop.

@ECHO off
REM SET FILE OUTPUT DIRECTORY
SET "filePath=%USERPROFILE%\Desktop\"
REM SET THE BASE OF THE NAME FOR THE OUTPUT FILE 
SET "baseName=NetworkInfo"
REM CREATE TIMESTAMP
SET timeStamp=%date:~6,8%%date:~3,2%%date:~0,2%%time:~0,2%%time:~3,2%%time:~6,2%
REM CREATE VARIABLE TO HOLD FILENAME WITH A TIMESTAMP
SET "fileNameStamp=%baseName%%Timestamp%"
SET "fileName=%filePath%%fileNameStamp%"
REM CAPTURE COMMAND OUTPUT TO TEXT FILE
> %fileName%.txt (
REM This is where the commands go!
echo Checking your network information, Please wait...

systeminfo | findstr /c:"Host Name" 
systeminfo | findstr /c:"Domain"

ipconfig /all | find "Physical Address" 

ipconfig | find "IPv4" 
ipconfig | find "Default Gateway"
)
Picture showing SQL Server Agent Properties Window

How to setup how much Agent Job History is retained

I was asked to do a historic analysis of SQL Server jobs to determine their statistics, such as the number of runs, average run duration, successes/failures etc. As part of the daily checks I do on a server I’d only check the job runs for the past 24 hours. So I was surprised when I started the analysis and saw that averages for the same job across servers varied significantly.

When I looked at the msdb.dbo.sysjobhistory table of the server with the lowest averages I noted that there were only 999 rows and the history only went back a day or two.

So I had effectively no history on that server. When I looked at another the sysjobhistory table had almost 10 million entries, roughly 4 Gb of data! (The joys of taking over someone else’s environment)

This got me looking at the history settings for the SQL Agent. To open the SQL Server Agent Properties while in SQL Server Object Explorer right click on SQL Server Agent and then click on properties.

Picture showing SQL Server Agent Properties Window

For the first server the history settings were set to the default of 1000 lines for the log as a whole and 100 lines for the individual jobs. If you have a job that runs 4 steps that will use 5 lines, one line for the job and one line for each of the steps that are run. When I looked at the history settings on the second, server as you would expect, no settings had been applied.

Some sensible thresholds needed to be applied across the environment. I’m not going to dictate to you what your retention policy should be regarding job run history but I will say it can be as much as business decision as a server maintenance one so ask around if people need a record that something has run.

You may be in the position that you can be specific enough to enter row values but most organisations will have jobs that run weekly or monthly. In that case you may be better off using the “Older than” option and setting a retention period rather than using rows.

Always with logging, if you have the space, you’re better off having too much than having too little and missing data you need.  The size of each row in the sysjobhistory table is at most 4.5KB so keeping 10k rows is only around 45 Mb. If you don’t have 45 Mb to spare on your server then job history retention is the least of your worries. The worst thing that is likely to happen in storing more rows than you need is that it takes a bit longer to bring up the job history viewer. But here’s a link to some queries to help with that.

The moral of the story is just because it’s in the MSDB doesn’t mean Microsoft are taking care of it. Check your settings and make sure you’re retaining an appropriate amount of history now.

How to lookup SQL Server Jobs and get the job history with a query

Sometimes job history just won’t load in SQL Server Management Studio for one reason or another. One of the main reasons is that there are too many entries in the sysjobhistory table. The article here will help you resolve that problem. For a more immediate answer to the data you are looking for, like most things with SSMS, you can query the tables that contain this data directly.

For a permanent solution to bypassing SSMS I recommend using this stored procedure. If you just want a quick query see below.

If you want to get a job history for everything that has run over the last 7 days you can run the query below. Simply change the 7 to another number to go further back in time by days.

-- Variable Declarations 
DECLARE @PreviousDate DATETIME
DECLARE @Year VARCHAR(4)
DECLARE @Month VARCHAR(2)
DECLARE @MonthPre VARCHAR(2)
DECLARE @Day VARCHAR(2)
DECLARE @DayPre VARCHAR(2)
DECLARE @FinalDate INT


-- Initialize Variables 
SET @PreviousDate = DATEADD(dd, - 7, GETDATE()) -- Last 7 days  
SET @Year = DATEPART(yyyy, @PreviousDate)

SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))

SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)), 2)

SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))

SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)), 2)

SET @FinalDate = CAST(@Year + @Month + @Day AS INT)

-- Pull Job History 
SELECT j.[name]
	,s.step_name
	,h.step_id
	,MSDB.DBO.AGENT_DATETIME(h.run_date, h.run_time) AS run_time
	,STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(h.run_duration AS VARCHAR(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') 'run_duration (DD:HH:MM:SS)  '
	,h.run_status
	,h.sql_severity
	,h.message
	,h.SERVER
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id
	AND h.step_id = s.step_id
WHERE h.run_date > @FinalDate
ORDER BY h.instance_id DESC

 

To get a job history for everything that has succeeded or failed over the last 7 days run the query below. Simply change the @RunStatus variable to either 0 (failed) or 1 (succeeded).

-- Variable Declarations 
DECLARE @RunStatus AS BIT
DECLARE @PreviousDate DATETIME
DECLARE @Year VARCHAR(4)
DECLARE @Month VARCHAR(2)
DECLARE @MonthPre VARCHAR(2)
DECLARE @Day VARCHAR(2)
DECLARE @DayPre VARCHAR(2)
DECLARE @FinalDate INT

/*Succeeded Jobs*/
--SET @RunStatus = 1
/*Failed Jobs*/
SET @RunStatus = 0

-- Initialize Variables 
SET @PreviousDate = DATEADD(dd, - 7, GETDATE()) -- Last 7 days  
SET @Year = DATEPART(yyyy, @PreviousDate)

SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))

SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)), 2)

SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))

SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)), 2)

SET @FinalDate = CAST(@Year + @Month + @Day AS INT)

-- Pull Job History 
SELECT j.[name]
	,s.step_name
	,h.step_id
	,MSDB.DBO.AGENT_DATETIME(h.run_date, h.run_time) AS run_time
	,STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(h.run_duration AS VARCHAR(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') 'run_duration (DD:HH:MM:SS)  '
	,h.run_status
	,h.sql_severity
	,h.message
	,h.SERVER
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id
	AND h.step_id = s.step_id
WHERE h.run_status = @RunStatus
	AND h.run_date > @FinalDate
ORDER BY h.instance_id DESC

 

If you want to generate a list of all the:

  1. jobs and their owners
  2. SSIS packages and their owners

you can do so by running the queries below. (If you don’t already know the precise name or ID of a job)

--Jobs
select s.name,l.name
 from  msdb..sysjobs s 
 left join master.sys.syslogins l on s.owner_sid = l.sid

--Packages
select s.name,l.name 
from msdb..sysssispackages s 
 left join master.sys.syslogins l on s.ownersid = l.sid

 

Once you have retrieved either the name (command) or the ID of the job you are looking for you can plug that info into either one of the queries below also.

use msdb

select *
from dbo.sysjobsteps with (nolock)
where command like '%YourJobName%'

select *
from dbo.sysjobs sj with (nolock)
where sj.job_id = '1234-1234-1234-1234-1234'