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.

Monday, June 18, 2012

Job_EnableJobsThatWereEnabled

This will be referred to in the next post.



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








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







Sunday, June 10, 2012

JobStatus

This proc JOINs the results of xp_sqlagent_enum_jobs with infraJobState to produce a list of jobs with the current status.  


Very useful on a busy server with lots of jobs kicking off at different times.

This proc is dependent upon these objects:
        infraJobState
        vwJob

Use Admin
GO
IF Object_ID('dbo.JobStatus') Is Not Null
      DROP PROC dbo.JobStatus
GO

CREATE PROC [dbo].[JobStatus]
      @IncludeIdle bit =      0
AS
/*    DATE        AUTHOR            REMARKS
      6/14/12           PPaiva            Initial creation.

      DESCRIPTION
            Poplulates a global temp table with all jobs, with Status.

      DEPENDENCIES
            master.dbo.xp_sqlagent_enum_jobs
            Admin.dbo.udfFormatTimeWithColons()
            Admin.dbo.vwJob
           
      DEBUG
            SELECT *
            FROM Admin..infraJobState
           
            SELECT *
            FROM msdb..sysjobactivity


      USAGE
            Exec Adminep..JobStatus      -- Only active jobs
            Exec Adminep..JobStatus 1    -- All jobs

            SELECT *
            FROM ##JobStatus
            ORDER BY 1

*/
SET NOCOUNT ON

IF Object_ID('tempdb.dbo.##JobStatus') Is Not Null
      DROP TABLE ##JobStatus



CREATE TABLE #JobState(
      job_id uniqueidentifier not null,
      last_run_date int not null,
      last_run_time int not null,
      next_run_date int not null,
      next_run_time int not null,
      next_run_schedule_id int not null,
      requested_to_run int not null, -- bool
      request_source int not null,
      request_source_id sysname collate database_default null,
      running int not null, -- bool
      current_step int not null,
      current_retry_attempt int not null,
      job_state int not null
      )

INSERT INTO #JobState
      Exec master.dbo.xp_sqlagent_enum_jobs
                  @is_sysadmin = 1,
                  @job_owner = ''

SELECT  i.Description Status,
            j.Server,
            j.Name,
            j.JobEn,
            j.SchedEn,
            js.request_source_id RequestedBy,
            js.current_step CurrStep,
            Convert(varchar(16),
                  CASE WHEN IsNull(JS.Last_Run_Date, 0) = 0 THEN Null
                         ELSE Convert(datetime,
                                          Convert(varchar, JS.Last_Run_Date) +
                                          ' ' + dbo.udfFormatTimeWithColons(Right('000000' + Convert(varchar, JS.Last_Run_Time), 6))
                                                      )
                        END, 120) LastRunDateTime,         
            j.NextRun,
            j.NextRunDateTime,
            j.Category,
            j.SchedName,
            j.Next_Run_Date,
            j.Next_Run_Time,
            j.JobCreated,
            j.JobModified,
            j.Description,
            j.Job_ID,
            j.OrderByMe,
            js.running
INTO ##JobStatus
FROM #JobState js
JOIN Admin.dbo.vwJob j
      ON j.job_id = js.job_id
JOIN Admin.dbo.infraJobState i
      ON i.ID = js.job_state
ORDER BY running desc

IF @IncludeIdle  = 1
      SELECT *
      FROM ##JobStatus
      ORDER BY 1
ELSE
      SELECT *
      FROM ##JobStatus
      WHERE Status <> 'Idle'
      ORDER BY 1



Print '-- All
SELECT *
FROM ##JobStatus
WHERE Name Like ''%%''
ORDER BY 1 desc

-- Exclude Idle
SELECT *
FROM ##JobStatus
WHERE Status <> ''Idle''
ORDER BY 1

'