About Me

Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group for 11 years, from January 2009 to 2020.

Wednesday, October 10, 2018

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

GO
IF OBJECT_ID('dbo.DropObjectIfExists') Is Not Null
       DROP PROC DropObjectIfExists
go
CREATE PROC dbo.DropObjectIfExists
       @Type varchar(2),
       @DB varchar(100),
       @Schema varchar(50),
       @Name varchar(300),
       @ShowSqlOnly bit = 0
AS
/*     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)



go


IF OBJECT_ID('dbo.Job_EnableJobsThatWereEnabled') Is Not Null
      DROP PROC dbo.Job_EnableJobsThatWereEnabled
GO

CREATE   PROC dbo.Job_EnableJobsThatWereEnabled
      @Debug bit = 0
AS
/*    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
go

IF OBJECT_ID('dbo.Job_DisableJobsThatAreEnabled') Is Not Null
      DROP PROC Job_DisableJobsThatAreEnabled
GO

 CREATE PROC dbo.Job_DisableJobsThatAreEnabled
      @Debug bit = 0
AS
/*    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
                  END
    
            ELSE
                  BEGIN
                        -- Summary of jobs
                        SELECT Enabled, Count(*) QtyJobs
                        FROM msdb.dbo.sysjobs
                        GROUP BY Enabled
                        ORDER BY 1


                  END

      END

go
IF OBJECT_ID('dbo.udfFormatJobNextRunDate') Is Not Null
       DROP FUNCTION dbo.udfFormatJobNextRunDate
GO
CREATE FUNCTION dbo.udfFormatJobNextRunDate(
      @NextRunDateTEXT varchar(8) = '',
      @NextRunTimeTEXT varchar(6)
      )
RETURNS Datetime
AS
/*  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

go
IF OBJECT_ID('dbo.vwJob') Is Not Null
       DROP VIEW vwJob
go
CREATE VIEW [dbo].[vwJob]
AS
/*  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

GO




No comments:

Post a Comment