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.

Thursday, November 1, 2018

vwBackupNever and vwBackupHistory (no dependencies)

Here is vwBackupNever and vwBackupHistory with no dependencies. 

This is created in tempdb but you can optionally put it into your Admin (or similarly named) database. 

Use this as a first check when you need to locate backup files.



/*    DATE        AUTHOR            REMARKS
      11/1/18     PPaiva            Initial creation.

      DESCRIPTION
            Use this code to get a historical list of backups on
                  this instance.
            First query result is Last backup of each database.

      USAGE
            -- Last backup of each database
            SELECT *
            FROM vwBackupHistory         
            WHERE Num = 1
            ORDER BY DateStarted desc

            -- Databases NEVER backed up
            SELECT *
            FROM vwBackupNever

            -- Last backups, all databases
            SELECT *
            FROM vwBackupHistory         
            ORDER BY DateStarted desc

            -- Last 5 backups per DB per Type per Path 
            SELECT *
            FROM vwBackupHistory
            WHERE Num <= 5
                  AND IsSysDB = 0
            ORDER BY Instance, DB, DateStarted desc

*/
Use tempdb
-- Use Admin  -- put this code in your DBA database if you have one
go
IF object_id('dbo.udfGetFilenameOnly') Is Not Null
      DROP FUNCTION dbo.udfGetFilenameOnly
go


CREATE FUNCTION dbo.udfGetFilenameOnly(
      @In varchar(500)
      )
RETURNS varchar(300)
AS
/*    DATE        AUTHOR            REMARKS
      2/2/16      PPaiva            Initial creation.


      USAGE
            SELECT dbo.udfGetFilenameOnly('D:\Program Files\Microsoft SQL Server\Logs\Admin_Log.LDF')
            SELECT dbo.udfGetFilenameOnly('Admin_Log.ldf')

*/

BEGIN
      DECLARE @Out varchar(300),
                  @Pos smallint
           
      -- Get position of last backslash
      SET @Pos = CharIndex('\', Reverse(@In))

      IF @Pos > 1
            BEGIN
                  SET @Pos = Len(@In) - @Pos               
                  SET @Out = Substring(@In, @Pos + 2, 500)
            END
     
      ELSE
            SET @Out = @In


      RETURN @Out

END
go

IF object_id('dbo.udfFormatInteger') Is Not Null
      DROP FUNCTION dbo.udfFormatInteger
go

CREATE FUNCTION dbo.udfFormatInteger(
      @In bigint
      )
RETURNS varchar(20)
AS
/*    DATE              AUTHOR            REMARKS   
      2/22/16           PPaiva            Initial creation.
    
      DESCRIPTION
            Formats a given integer with commas.
                  Examples:
                              IN                OUT
                              123               123
                            12345            12,345
                           -54321           -54,321

      USAGE
            SELECT dbo.udfFormatInteger(1234567890)
            SELECT dbo.udfFormatInteger(12345)
            SELECT dbo.udfFormatInteger(-54321)
            SELECT dbo.udfFormatInteger(0)
*/

BEGIN
      DECLARE @Out varchar(20),
                  @sIn varchar(20),
                  @Balance varchar(20),
                  @CurrTextLen smallint,
                  @IsNegative bit

      IF @In < 0
            BEGIN
                  SET @IsNegative = 1
                  SET @In = Abs(@In)
            END

      SET @sIn = Convert(varchar, @In)
      SET @CurrTextLen = Len(@sIn)
      SET @Out = ''
      SET @Balance = @sIn

      IF @CurrTextLen > 3
            BEGIN
                  WHILE 1 = 1
                        BEGIN
                              SET @Out = ',' + Right(@Balance, 3) + @Out
                              SET @Balance = Substring(@sIn, 1, @CurrTextLen - 3)

                              SET @CurrTextLen = Len(@Balance)
                              IF @CurrTextLen > 3
                                    CONTINUE
                              ELSE
                                    BEGIN
                                          SET @Out = @Balance + @Out
                                          BREAK
                                    END 
                        END
            END

      ELSE
            SET @Out = @sIn

      IF @IsNegative = 1
            SET @Out = '-' + @Out

      RETURN @Out

END
go

IF object_id('dbo.udfGetPathOnly') Is Not Null
      DROP FUNCTION dbo.udfGetPathOnly
go

CREATE FUNCTION dbo.udfGetPathOnly(
      @In varchar(1000)
)
RETURNS varchar(1000)
AS
/*    DATE        AUTHOR            REMARKS
      2/22/16     PPaiva            Initial creation.


      DESCRIPTION
            Returns the path only, with a trailing backslash. 

      USAGE
            SELECT dbo.udfGetPathOnly('C:\Windows\Notepad.exe')
            SELECT dbo.udfGetPathOnly('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\tempdb.mdf')
            SELECT dbo.udfGetPathOnly('tempdb.ldf')
            SELECT dbo.udfGetPathOnly('C:\asdf')
            SELECT dbo.udfGetPathOnly('C:')
            SELECT dbo.udfGetPathOnly('Z:')

*/
BEGIN
      DECLARE @Out varchar(300),
                  @PosSlash smallint,
                  @PosDot smallint


      -- Get position of last backslash
      SET @PosSlash = CharIndex('\', Reverse(@In))
      -- Get position of last dot
      SET @PosDot = CharIndex('.', Reverse(@In))

      IF @PosDot > 0
            SET @PosDot = Len(@In) - @PosDot + 1

      IF @PosSlash > 0
            BEGIN
                  SET @PosSlash = Len(@In) - @PosSlash + 1
                  IF @PosDot = 0
                        SET @Out = @In
                  ELSE
                        SET @Out = Left(@In, @PosSlash)
            END

      ELSE
            IF Len(@In) = 2 AND Right(@In, 1) = ':'
                  SET @Out = @In + '\'


      RETURN @Out

END
go



IF Object_ID('dbo.vwBackupHistory') Is Not Null
      DROP VIEW dbo.vwBackupHistory
GO

CREATE VIEW dbo.vwBackupHistory
AS
/*  DATE        AUTHOR          REMARKS
       11/29/16    PPaiva          Initial creation.
       11/1/18              PPaiva               Added column IsOnline.

    
       -- Last 10 backups per DB per Type per Path 
       SELECT *
       FROM vwBackupHistory
       WHERE Num <= 10
       AND DB Not In ('master', 'model', 'msdb')
       ORDER BY 1, 2, 3, 4, 5, 6

       SELECT *
       FROM vwBackupHistory
       ORDER BY DateStarted desc

       -- DBs backed up
       SELECT Machine, SqlInstance, DB, BackupType, RecoveryModel, Path, SoftwareName, Count(*) Qty
       FROM vwBackupHistory
       GROUP BY Machine, SqlInstance, DB, BackupType, RecoveryModel, Path, SoftwareName
       ORDER BY Machine, SqlInstance, DB, BackupType, RecoveryModel, Path, SoftwareName

       SELECT *
       FROM vwBackupHistory
       WHERE BackupType <> 'Log'
       ORDER BY DateFinished desc,   DB


       SELECT Machine, SqlInstance, DB, BackupType, RecoveryModel, Path, SoftwareName, Count(*) Qty
       FROM vwBackupHistory
       GROUP BY Machine, SqlInstance, DB, BackupType, RecoveryModel, Path, SoftwareName
       ORDER BY Machine, SqlInstance, DB, BackupType, RecoveryModel, Path, SoftwareName

*/
SELECT  ROW_NUMBER() OVER (PARTITION BY  b.database_name, b.Type, b.Recovery_Model ORDER BY backup_start_date desc) Num,
            ServerProperty('ServerName') Instance,
        b.database_name DB,
              CASE WHEN d.name Is Not Null THEN 1
                           ELSE 0 END IsOnline,
        CASE b.type
              WHEN 'D' THEN 'Database'
              WHEN 'L' THEN 'Log'
              WHEN 'I' THEN 'Differential Database'
              WHEN 'F' THEN 'File or FileGroup'
              WHEN 'G' THEN 'Differential File'
              WHEN 'P' THEN 'Partial'
              WHEN 'Q' THEN 'Differential Partial'
              WHEN Null THEN 'Null'
              ELSE 'Need to define in view'
                    END AS BackupType,
        b.recovery_model RecoveryModel,
        b.backup_start_date DateStarted,
              DATEDIFF(second, b.backup_start_date, b.backup_finish_date) BackupSecs,
              DATEDIFF(minute, b.backup_start_date, b.backup_finish_date) BackupMins,
              dbo.udfFormatInteger((b.backup_size /10000.)) SizeMBFmt,
            CASE WHEN b.database_name In ('master', 'model', 'msdb', 'tempdb', 'distribution', 'Admin')
                        THEN 1 ELSE 0
                              END IsSysDB,
              dbo.udfGetFilenameOnly(f.physical_device_name) Filename,
              dbo.udfGetPathOnly(f.physical_device_name) Path,
            bms.is_compressed IsCompressed,                             -- remove column for 2005 and lower
            b.compressed_backup_size CompressedSizeBytes,   -- remove column for 2005 and lower
             (Convert(decimal(4,1), (100 * (b.compressed_backup_size / b.backup_size))) ) PercComp,  -- remove column for 2005 and lower
        b.backup_size SizeBytes,
        b.is_copy_only IsCopy,
        b.is_damaged IsDamaged,
        b.description,
        b.expiration_date ExpDate,
        f.physical_device_name PathFilename,
        b.name AS BackupSetName,
        f.logical_device_name LogicalDeviceName,
        fg.name FileGroup,
        bms.software_name SoftwareName,
        b.backup_finish_date DateFinished
FROM msdb.dbo.backupmediafamily  f
JOIN msdb.dbo.backupset b
      ON f.media_set_id = b.media_set_id
JOIN msdb.dbo.backupmediaset bms
      ON bms.media_set_id = b.media_set_id
JOIN msdb.dbo.backupfilegroup fg
      ON fg.backup_set_id = b.backup_set_id
LEFT JOIN sys.databases d
       ON d.name = b.database_name
go
IF Object_ID('dbo.vwBackupNever') Is Not Null
      DROP VIEW dbo.vwBackupNever
GO
CREATE VIEW dbo.vwBackupNever
AS
/*     DATE        AUTHOR            REMARKS
       11/1/18              PPaiva            Initial creation.
    
       SELECT *
       FROM vwBackupNever
       ORDER BY Name

*/

-- Databases NEVER backed up
SELECT  s.Name,
              s.state_desc State,
              S.recovery_model_desc RecoveryModel
FROM sys.databases s
LEFT JOIN (SELECT DB FROM vwBackupHistory WHERE Num = 1) as v
        ON v.DB = s.name
WHERE v.DB Is null
go



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