A Developer's Musings on SQL Server Administration: Taming a Busy SQL Agent

Scenario
Need to shut down a busy server to do some maintenance, but you are finding this challenging because there are many jobs that are executing and about-to-execute?

Steps
1. Save the state (active or disabled) of all your jobs.
2. Disable all jobs programatically.
3. After jobs finish executing, shut down the Agent or the Instance, do your necessary maintenance.
4. Turn instance back on.
5. Enable jobs that were previously disabled.

Creates these objects:
Primary objects for automatically disabling/enabling jobs
Job_DisableJobsThatAreEnabled
Job_EnableJobsThatWereEnabled

Dependent objects
udfFormatJobNextRunDate
DropObjectIfExists

Helpful view
vwJob

Use Admin -- Or the database of your choice

IF OBJECT_ID('dbo.DropObjectIfExists') Is Not Null

DROP PROC DropObjectIfExists

CREATE PROC dbo.DropObjectIfExists

@Type varchar(2),

@DB varchar(100),

@Schema varchar(50),

@Name varchar(300),

@ShowSqlOnly bit = 0

/* DATE AUTHOR REMARKS

3/8/17 PPaiva Initial creation.

DESCRIPTION

Drops certain objects.

Values for @xType

USAGE

CREATE TABLE dbo.DropMeTest(ID int)

CREATE PROC dbo.DropMeTestProc AS SELECT 1

Exec Admin..DropObjectIfExists 'u', 'Admin', 'dbo', 'DropMeTest', 1

Exec Admin..DropObjectIfExists 'u', 'Admin', 'dbo', 'DropMeTest'

Exec Admin..DropObjectIfExists 'p', 'Admin', 'dbo', 'DropMeTestProc', 1

Exec Admin..DropObjectIfExists 'p', 'Admin', 'dbo', 'DropMeTestProc'

DEBUG

SELECT *

FROM Admin..infraObjectType

SELECT o.type, o.type_desc, s.name SchemaName, o.name, o.create_date, o.modify_date, o.object_id

FROM [dba].sys.objects o

JOIN [dba].sys.schemas s

ON o.schema_id = s.schema_id

WHERE o.name = 'DropMeTest'

AND s.name = 'dbo'

AND o.Type = 'u'

SET NOCOUNT ON

DECLARE @sSelect varchar(1000),

@sDropObject varchar(30),

@sDrop varchar(1000)

IF @Type Not In ('u', 'v', 'p', 'fn', 'if', 'tf')

BEGIN

SET @sSelect = 'Acceptable values for @Type are:

u (table)

v (view)

p (proc)

fn (scalar function)

if (inlined table-function)

tf (table function)'

RaisError(@sSelect, 16, 1)

RETURN

END

SET @sSelect = ' SELECT o.type, o.type_desc, s.name SchemaName, o.name, o.create_date, o.modify_date, o.object_id

FROM [' + @DB + '].sys.objects o

JOIN [' + @DB + '].sys.schemas s

ON o.schema_id = s.schema_id

WHERE o.name = ''' + @Name + '''

AND s.name = ''' + @Schema + '''

AND o.Type = ''' + @Type + ''''

SET @sDropObject = CASE @Type WHEN 'u' THEN 'TABLE'

WHEN 'v' THEN 'VIEW'

WHEN 'p' THEN 'PROC'

WHEN 'fn' THEN 'FUNCTION'

WHEN 'if' THEN 'FUNCTION'

WHEN 'tf' THEN 'FUNCTION'

ELSE 'NotDefined'

END

-- Dropping an object other than table does not allow specifiying the

-- database name, thus database context is set with USE

SET @sDrop = 'IF Exists(' + @sSelect + '

)

BEGIN

USE ' + @DB + '

DROP ' + @sDropObject + ' [' + @Schema + '].[' + @Name + ']

END'

IF @ShowSqlOnly = 1

BEGIN

Exec(@sSelect)

Print @sDrop

END

ELSE

Exec(@sDrop)

IF OBJECT_ID('dbo.Job_EnableJobsThatWereEnabled') Is Not Null

DROP PROC dbo.Job_EnableJobsThatWereEnabled

CREATE PROC dbo.Job_EnableJobsThatWereEnabled

@Debug bit = 0

/* DATE AUTHOR REMARKS

6/19/12 Paiva Initial creation.

DESCRIPTION

Enables the jobs that were previously disabled

via Job_DisableJobsThatAreEnabled.

USAGE

Job_EnableJobsThatWereEnabled 1

Job_EnableJobsThatWereEnabled

DEBUG

SELECT Enabled, *

FROM msdb.dbo.JobsBUPreviouslyEnabled

SET NOCOUNT ON

DECLARE @LastBUDate datetime,

@NumEnabled smallint,

@NumDisabled smallint,

@Sql varchar(300)

IF OBJECT_ID('msdb.dbo.JobsBUPreviouslyEnabled') Is Not Null

BEGIN

-- Get stats of last backkup

SELECT @LastBUDate = Max(InsertDate),

@NumEnabled = Count(*)

FROM msdb.dbo.JobsBUPreviouslyEnabled

WHERE Enabled = 1

SELECT @LastBUDate = Max(InsertDate),

@NumDisabled = Count(*)

FROM msdb.dbo.JobsBUPreviouslyEnabled

WHERE Enabled = 0

END

ELSE

BEGIN

SET @Sql = 'Cannot enable jobs that were previously disabled because a backup was not made.'

SET @Sql = @Sql + Char(13) + Char(10) + 'To make backup and disable jobs use < Job_DisableJobsThatAreEnabled >.'

RaisError (@Sql, 16, 1)

RETURN

END

IF @Debug = 1

BEGIN

SET @Sql = ' Date of last backup: ' +Convert(varchar, @LastBUDate, 120)

SET @Sql = @Sql + Char(13) + Char(10) + ' Num Previously Enabled: ' + Convert(varchar, @NumEnabled)

SET @Sql = @Sql + Char(13) + Char(10) + 'Num Previously Disabled: ' + Convert(varchar, @NumDisabled)

Print @Sql

RETURN

END

-- Re-Enable the previously enabled jobs

DECLARE @JobID varchar(38),

@MaxJobID varchar(38),

@JobName nvarchar(256),

@Num smallint

SELECT @MaxJobID = Max(Convert(varchar(38), Job_ID)),

@Num = Count(*),

@JobID = ''

FROM msdb.dbo.JobsBUPreviouslyEnabled

WHERE Enabled = 1

BEGIN TRANSACTION

WHILE @JobID < @MaxJobID

BEGIN

-- Get next JobID

SELECT @JobID = Min(Convert(varchar(38), Job_ID))

FROM msdb.dbo.JobsBUPreviouslyEnabled

WHERE Enabled = 1

AND Convert(varchar(38), Job_ID) > @JobID

-- Get other attributes

SELECT @JobName = Name

FROM msdb.dbo.JobsBUPreviouslyEnabled

WHERE Convert(varchar(38), Job_ID) = @JobID

SET @Sql = 'Exec msdb.dbo.sp_update_job @Job_ID = ''' +@JobID + ''', @Enabled = 1'

--Print @Sql

Exec (@Sql)

END

Exec dbo.DropObjectIfExists 'u', 'msdb', 'dbo','JobsBUPreviouslyEnabled'

COMMIT

Print 'Number of jobs that were enabled: ' + Convert(varchar, @Num)

SELECT Name CurrentlyEnabledJobName, Job_ID JobID, Enabled

FROM msdb.dbo.sysjobs

WHERE Enabled = 1

ORDER BY Name

SELECT Name CurrentlyDisabledJobName, Job_ID JobID, Enabled

FROM msdb.dbo.sysjobs

WHERE Enabled = 0

ORDER BY Name

IF OBJECT_ID('dbo.Job_DisableJobsThatAreEnabled') Is Not Null

DROP PROC Job_DisableJobsThatAreEnabled

CREATE PROC dbo.Job_DisableJobsThatAreEnabled

@Debug bit = 0

/* DATE AUTHOR REMARKS

6/19/12 PPaiva Initial creation.

DESCRIPTION

1. Checks for a previous backup of table JobsBUPreviouslyEnabled.

2. Backs up sysjobs in msdb to JobsBUPreviouslyEnabled in msdb.

3. Disables all jobs that were enabled.

4. Calls out sp_update job for each job, so that the Agent

cache is refreshed.

NOTES

A. This is useful if you want to shut down SQL Agent but don't

want to do it while any jobs are executing, and if you don't

want to manually disable all the jobs.

B. Run this proc, then wait for jobs to finish executing, then

shut down SQL Agent.

C. Do whatever task you have in mind.

D. When SQL Agent is running again and you are ready to return

the jobs to their original state, run <Job_EnableJobsThatWereEnabled>.

USAGE

-- To disable jobs, show SQL only

Exec Job_DisableJobsThatAreEnabled 1

-- To disable jobs, Execute

Exec Job_DisableJobsThatAreEnabled

-- After running the above, verify that all jobs are disabled

SELECT *

FROM vwJob

-- When ready to re-enable the jobs, run

Exec Job_EnableJobsThatWereEnabled 1 -- Show stats only

Exec Job_EnableJobsThatWereEnabled -- Execute

DEBUG

Exec dbo.DropObjectIfExists 'u', 'msdb', 'dbo', 'JobsBUPreviouslyEnabled'

SELECT *

FROM msdb.dbo.JobsBUPreviouslyEnabled

WHERE Enabled = 1

SELECT *

FROM msdb.dbo.sysjobs

WHERE Enabled = 1

SELECT *

FROM msdb..JobsBUPreviouslyEnabled

Exec Admin.dbo.JobStatus

SET NOCOUNT ON

DECLARE @LastBUDate datetime,

@Sql varchar(300)

IF @Debug = 0

BEGIN

IF OBJECT_ID('msdb.dbo.JobsBUPreviouslyEnabled') Is Not Null

BEGIN

Print 'Table < msdb.dbo.JobsBUPreviouslyEnabled > already exists.

You may first need to run < Job_EnableJobsThatWereEnabled > so as

to restore job enabled/disabled attributes.'

RETURN

END

-- Back up job info

SELECT GetDate() InsertDate, *

INTO msdb.dbo.JobsBUPreviouslyEnabled

FROM msdb.dbo.sysjobs

ALTER TABLE msdb.dbo.JobsBUPreviouslyEnabled ADD CONSTRAINT

PK_JobsBUPreviouslyEnabled PRIMARY KEY (Job_ID)

DECLARE @JobID varchar(38),

@MaxJobID varchar(38),

@JobName sysname,

@Num smallint

SELECT @MaxJobID = Max(Convert(varchar(38), Job_ID)),

@Num = Count(*),

@JobID = ''

FROM msdb.dbo.JobsBUPreviouslyEnabled

WHERE Enabled = 1

-- Diable jobs

WHILE @JobID < @MaxJobID

BEGIN

-- Get next JobID

SELECT @JobID = Min(Convert(varchar(38),Job_ID))

FROM msdb.dbo.JobsBUPreviouslyEnabled

WHERE Enabled = 1

AND Convert(varchar(38), Job_ID) > @JobID

-- Get other attributes

SELECT @JobName = Name

FROM msdb.dbo.JobsBUPreviouslyEnabled

WHERE Convert(varchar(38), Job_ID) = @JobID

SET @Sql = 'Exec msdb.dbo.sp_update_job @Job_ID = ''' + @JobID + ''', @Enabled = 0'

IF @Debug = 1

Print @Sql

ELSE

Exec (@Sql)

END

Print '-- Number of jobs that were disabled: ' +Convert(varchar, @Num)

Print ''

SELECT Name EnabledJobName, Job_ID JobID, Enabled

FROM msdb.dbo.JobsBUPreviouslyEnabled

WHERE Enabled = 1

ORDER BY Name

SELECT Name DisabledJobName, Job_ID JobID, Enabled

FROM msdb.dbo.JobsBUPreviouslyEnabled

WHERE Enabled = 0

ORDER BY Name

Print 'To re-enable these jobs, run < Exec Admin.dbo.Job_EnableJobsThatWereEnabled >.'

END

ELSE IF @Debug = 1

BEGIN

IF Object_ID('msdb.dbo.JobsBUPreviouslyEnabled') Is Not Null

BEGIN

-- Get date of last BU

SELECT @LastBUDate = crDate

FROM msdb.dbo.sysobjects

WHERE Name = 'JobsBUPreviouslyEnabled'

AND xType = 'u'

-- Check for previous Back Up

IF @LastBUDate Is Not Null

BEGIN

SET @Sql = 'There is already a backup from ' + Convert(varchar, @LastBUDate, 120) + '.'

SET @Sql = @Sql + Char(13) +Char(10) + 'Consider running < Exec Job_EnableJobsThatWereEnabled > to restore all jobs to original state.'

SET @Sql = @Sql + Char(13) +Char(10) + 'Or, to permanently destroy the last back up, make a new backup, and disable all jobs, rerun this proc as such:'

SET @Sql = @Sql + Char(13) +Char(10) + ' Exec Job_DisableJobsThatAreEnabled 1'

RaisError (@Sql, 0, 1)

SELECT Name EnabledJobName,Job_ID JobID, Enabled

FROM msdb.dbo.JobsBUPreviouslyEnabled

WHERE Enabled = 1

ORDER BY Name

SELECT Name DisabledJobName,Job_ID JobID, Enabled

FROM msdb.dbo.JobsBUPreviouslyEnabled

WHERE Enabled = 0

ORDER BY Name

RETURN

END

ELSE

BEGIN

-- Summary of jobs

SELECT Enabled, Count(*) QtyJobs

FROM msdb.dbo.sysjobs

GROUP BY Enabled

ORDER BY 1

END

IF OBJECT_ID('dbo.udfFormatJobNextRunDate') Is Not Null

DROP FUNCTION dbo.udfFormatJobNextRunDate

CREATE FUNCTION dbo.udfFormatJobNextRunDate(

@NextRunDateTEXT varchar(8) = '',

@NextRunTimeTEXT varchar(6)

)

RETURNS Datetime

/* DATE AUTHOR REMARKS

11/21/16 PPaiva Initial creation

DESCRIPTION

Formats the text columns Next_Run_Date and Next_Run_Time

in sysJobSchedules to a datetime format.

TIME formatting

Input: 4000

Output: 00:40:00

USAGE

SELECT *,

dbo.udfFormatJobNextRunDate(next_run_date, next_run_time) ProperlyFormattedDatetime

FROM msdb.dbo.sysJobSchedules

ORDER BY len(next_run_time)

SELECT dbo.udfFormatJobNextRunDate('', '3')

SELECT dbo.udfFormatJobNextRunDate('20160401', '3')

SELECT dbo.udfFormatJobNextRunDate('20160401', '2300')

SELECT dbo.udfFormatJobNextRunDate('20160401', '12300')

SELECT dbo.udfFormatJobNextRunDate('20160401', '112300')

BEGIN

DECLARE @Out datetime,

@NextRunTimeWithColons varchar(8),

@LenTime int

-- Pad left with zeros

SET @LenTime = Len(@NextRunTimeTEXT)

SET @NextRunTimeTEXT = REPLICATE('0', 6 - @LenTime) + @NextRunTimeTEXT

-- Add colons. Change 005000 to 00:50:00

SET @NextRunTimeWithColons = Left(@NextRunTimeTEXT, 2)

+ ':'

+ Substring(@NextRunTimeTEXT, 3, 2)

+ ':'

+ Right(@NextRunTimeTEXT, 2)

IF @NextRunDateTEXT = ''

SET @Out = @NextRunTimeWithColons

ELSE

-- Concatenate date and time

SET @Out = CASE WHEN IsNull(@NextRunDateTEXT, 0) = 0 THEN Null

ELSE Convert(datetime, @NextRunDateTEXT + ' ' + @NextRunTimeWithColons

)

END

RETURN @Out

END

IF OBJECT_ID('dbo.vwJob') Is Not Null

DROP VIEW vwJob

CREATE VIEW [dbo].[vwJob]

/* DATE AUTHOR REMARKS

2/21/16 PPaiva Initial creation.

DESCRIPTION

Returns list of jobs.

Column SchedEn looks at all schedules for a job. If any one is

enabled, then SchedEn = 1.

USAGE

SELECT *

FROM vwJob

ORDER BY Instance, JobEn desc, Name

WITH cteJobNextRunSchedEn

AS (

SELECT job_id,

Min(Convert(varchar(16), dbo.udfFormatJobNextRunDate(next_run_date, next_run_time), 120)) NextRunDatetime,

CASE WHEN Sum(Convert(int, sched.enabled)) > 0

THEN 1

ELSE 0

END SchedEn,

Count(*) NumSched

FROM msdb.dbo.sysjobschedules jobsched

JOIN msdb.dbo.sysschedules sched

ON sched.schedule_id = jobsched.schedule_id

GROUP BY job_id

)

SELECT Convert(varchar(50), ServerProperty('ServerName')) Instance,

J.Enabled JobEn,

SchedEn.SchedEn,

IsNull(SchedEn.NumSched, 0) NumSched,

J.Name,

J.Description,

SchedEn.NextRunDateTime,

CASE WHEN C.Name = '[Uncategorized (Local)]' THEN ''

ELSE c.Name END Category,

-- Originating_Server OrigServer,

msdb.dbo.SQLAGENT_SUSER_SNAME(owner_sid) Owner,

J.Category_ID,

J.Date_Created JobCreated,

J.Date_Modified JobModified,

J.Job_ID

FROM msdb.dbo.sysJobs J

LEFT JOIN msdb.dbo.syscategories C

ON C.Category_ID = J.Category_ID

LEFT JOIN cteJobNextRunSchedEn SchedEn

ON SchedEn.job_id = j.job_id

A Developer's Musings on SQL Server Administration

About Me

Wednesday, October 10, 2018

Taming a Busy SQL Agent

No comments:

Post a Comment