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

'

infraJobState - State of a Job

Have you ever wondered how to interpret the State column when running the undocumented extended stored procedure xp_sqlagent_enum_jobs?  I haven't found this info in a native system table, so I created this table.  In fact I have a series of tables (I call them my infrastructure tables) which contain other snippets of info not found in the system tables (more on those later).

This proc creates table infraJobState.  You can then JOIN this with the results of  xp_sqlagent_enum_jobs  and come up with a list of jobs with current status.  See post xxx.

IF object_id('dbo.CreateInfraJobState') Is NOT Null

      DROP PROC dbo.CreateInfraJobState

go

CREATE PROC dbo.CreateInfraJobState

AS

/*    DATE        AUTHOR            REMARKS

      6/2/12            PPaiva            Initial creation.

     

      DESCRIPTION

            Creates table infraJobState.  This infrastructure table is useful

                  for mapping the State column when xp_sqlagent_enum_jobs is

                  executed.  I haven't found this info in a native system table

                  as yet.  Let me know if you do!

     

            Exec master.dbo.xp_sqlagent_enum_jobs

                        @is_sysadmin = 1,

                        @job_owner = ''

                 

      USAGE

            CreateInfraJobState

           

      DEBUG

            SELECT *

            FROM infraJobState

                       

*/

SET NOCOUNT ON 

IF object_id('dbo.infraJobState') Is Not Null

      DROP TABLE dbo.infraJobState

CREATE TABLE dbo.infraJobState(

      ID int NOT NULL CONSTRAINT pk_infraJobState PRIMARY KEY CLUSTERED,

      Description varchar(50)

      )

INSERT INTO infraJobState VALUES (0, 'Not idle or suspended')

INSERT INTO infraJobState VALUES (1, 'Executing')

INSERT INTO infraJobState VALUES (2, 'Waiting For Thread')

INSERT INTO infraJobState VALUES (3, 'Between Retries')

INSERT INTO infraJobState VALUES (4, 'Idle')

INSERT INTO infraJobState VALUES (5, 'Suspended')

INSERT INTO infraJobState VALUES (6, 'Waiting for Step to Finish')

INSERT INTO infraJobState VALUES (7, 'Performing Completion Actions')

CreateVwTrigger

Here’s a procedure that creates a view that lists all triggers in all tables in all databases on a given instance.

USE Admin

GO

IF object_id('CreateVwTrigger') Is Not Null

      DROP PROC dbo.CreateVwTrigger

GO

CREATE PROC dbo.CreateVwTrigger

      @ShowSql bit = 0

AS

/*    DATE        AUTHOR            REMARKS

      6/1/12            PPaiva            Intial creation.

      DESCRIPTION

            Creates a view in the Admin database for all triggers

                  in all non-system databases on this server.

            System objects are excluded.

            Excluded ReportServer databases due to collation conflicts

                  in the UNION.

            Coded to use Convert(varchar, ServerProperty('ServerName'))

                  rather than @@ServerName since the latter can be erroneous.

      USAGE

            Exec Admin..CreateVwTrigger   1

            SELECT *

            FROM Admin.dbo.vwTrigger

            ORDER BY DB, TableName, TriggerName

            -- DBs that have triggers

            SELECT DB, Count(*) Qty

            FROM Admin.dbo.vwTrigger

            GROUP BY DB

            ORDER BY DB

*/

SET NOCOUNT ON

DECLARE @s varchar(max),

            @DB varchar(100),

            @MaxDB varchar(100),

            @i int,

            @sNow varchar(16)

SET @sNow = Convert(varchar(16), GetDate(), 120)

SET @s = 'IF Object_ID(''dbo.vwTrigger'') Is Not Null

      DROP VIEW dbo.vwTrigger'

IF @ShowSql = 1

      Print @s

Exec(@s)

     

Print ''

SET @s = 'CREATE VIEW dbo.vwTrigger

AS

/*    DATE        AUTHOR            REMARKS

      ' + Convert(varchar(10), GetDate(), 01) + '     PPaiva            Intial creation.

      DESCRIPTION

            Provides a list of triggers for all non-system databases

                  on this server.

      SELECT *

      FROM Admin..vwTrigger

*/

'

-- DROP TABLE #DBs

SELECT  *

INTO #TrigDBs

FROM sys.databases

--SELECT *

--FROM #DBs

--ORDER BY Name

SELECT  @MaxDB = Max(Name),

            @DB = '',

            @i = 0

FROM #TrigDBs

WHILE @DB < @MaxDB

      BEGIN

            SET @i = @i + 1

            SELECT @DB = Min(Name)

            FROM #TrigDBs          

            WHERE Name > @DB

            SET @s = @s + '  

SELECT  Convert(varchar, ServerProperty(''ServerName'')) as Server,

      ''' + @DB + ''' as DB,

      ta.Name COLLATE SQL_Latin1_General_CP1_CI_AS TableName,

      tr.Name COLLATE SQL_Latin1_General_CP1_CI_AS TriggerName,

      ta.crdate TableCreateDate,

      tr.crdate TriggerCreateDate,

''' + @sNow + ''' ViewCreateDate

FROM [' + @DB + '].dbo.sysobjects tr

JOIN [' + @DB + '].dbo.sysobjects ta

ON tr.parent_obj = ta.ID

WHERE tr.xtype = ''tr''

'

                       

            IF @DB <> @MaxDB

                  SET @s = @s + 'UNION ALL '

      END

IF @ShowSql = 1

      BEGIN

            SELECT *

            FROM #TrigDBs

            ORDER BY Name

            Print @s

      END

           

Exec (@s)

vwBackupHistory

Here is a view that JOINs the pertinent system tables in msdb to present a concise view of backup history.

This view has three dependencies which are easy to install:

        udfFormatInteger

        udfGetFilenameOnly

        udfGetPathOnly

USE Admin

GO

IF object_id('vwBackupHistory') Is Not Null

      DROP VIEW dbo.vwBackupHistory

GO

CREATE VIEW dbo.vwBackupHistory

AS

/*    DATE        AUTHOR            REMARKS

      5/20/12           PPaiva            Initial creation.

     

      -- 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  b.machine_name Machine,

            b.server_name SqlInstance,

            b.database_name DB, 

            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,

            ROW_NUMBER() OVER (PARTITION BY b.Machine_name, b.server_name, b.database_name, b.Type, b.Recovery_Model ORDER BY backup_start_date desc) Num,

            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 /1000000.)) SizeMBFmt,

                  dbo.udfGetFilenameOnly(f.physical_device_name) Filename,

                  dbo.udfGetPathOnly(f.physical_device_name) Path,

            bms.is_compressed IsCompressed,             -- add this column for 2008 and higher

            b.compressed_backup_size CompressedSizeBytes,      -- add this column for 2008 and higher

            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

GO

Attach Database without Log File

If you only have the data file for a database, it is possible to attach it with the “ATTACH_REBUILD_LOG” option which will create a log file.

I found this was a necessary method when attempting to install the AdventureWorks databases for SQL 2012.  The .mdf files can be downloaded from the CodePlex site, but no log files are given.  

CREATE DATABASE AdventureWorks2012

ON (FILENAME = 'C:\Paul\Sql\Data\AdventureWorks2012_Data.mdf')

FOR ATTACH_REBUILD_LOG

CREATE DATABASE AdventureWorksDW2012

ON (FILENAME = 'C:\Paul\Sql\Data\AdventureWorksDW2012_Data.mdf')

FOR ATTACH_REBUILD_LOG

Thanks to Dave Lassiter for this tip.

Pagination Example

SQL Server’s TOP clause allows you to SELECT n rows from a result set. 

Unfortunately, these rows are always from the top.  If for example you have a large set of rows, and you have a front-end interface that allows you to show a given page, SQL Server does not have a native method for scrolling down p pages and retrieving only the rows for that page.

But, thanks to the  ROW_NUMBER()  built-in function in combination with a Common Table Expression (CTE), you can create a proc that does exactly what you need.

USE Admin

IF Object_id('dbo.PaginationExample') Is Not Null

      DROP PROC dbo.PaginationExample

GO

CREATE PROC dbo.PaginationExample

      @PageNum int,

      @RowsPerPage int

AS

/*    DATE        AUTHOR            REMARKS

      5/1/12            PPaiva            Initial creation.

     

      DESCRIPTION

            Selects a set of rows for a given page.

            To use, simply swap your table/view name for

                  vwServerDBFile, and change the 

                  ORDER BY clause in the CTE.

           

      USAGE

            Exec PaginationExample 2, 5

            Exec PaginationExample 3, 10

           

      DEBUG

            SELECT *

            FROM vwServerDBFIle

            ORDER BY Server, DB, Usage desc, Filename

*/

SET NOCOUNT ON

DECLARE @StartRow int,

            @EndRow int,

            @Msg varchar(300)

SET @StartRow = (@RowsPerPage * (@PageNum - 1) + 1)

SET @EndRow = @StartRow + (@RowsPerPage - 1)

; WITH cteOutput

AS ( 

      SELECT ROW_NUMBER() OVER (ORDER BY Server, DB, Usage desc, Filename) AS RowNum,

                  *

      FROM vwServerDBFIle

)

SELECT *

FROM cteOutput

WHERE RowNum BETWEEN @StartRow AND @EndRow

ORDER BY RowNum

SQL 2012 Installation Error

I encountered this error while installing SQL 2012.  What is puzzling is that I was using a fully licensed copy that I had downloaded from Microsoft using my BizSpark account.  I encountered this error for both Developer and Enterprise editions.

SQL Server Setup has encountered the following error:

The SQL Server product key is not valid.  To proceed, re-enter the product key values from the Certificate of Authenticity (COA) or SQL Server packaging.

Error code 0x858C0017.

I was installing SQL 2012 onto a virtual machine (in my case, using Virtual Box from Oracle Corporation).  The setup file for SQL was located on my host machine, but the VM had access to that folder. 

This had me perplexed for days.  Finally I solved the problem by copying the whole install image onto the VM.  Once setup accessed the files locally to the VM rather than from the host, I was able to install without any problem.