About Me

My photo
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 from January 2009 to present.

Sunday, June 17, 2012

Job_DisableJobsThatAreEnabled

This will be referred to in a future post.



USE Admin
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







No comments:

Post a Comment