Category Archives: Uncategorized

SQL Agent Job Frequency Intervals in Plain English

A DBA I was talking to was trying to create a simple process that finds each SQL Agent Job on a given instance, extracts the basic information regarding it, and compiles a list that can be presented to non-DBAs for information about what sort of “undiscovered” processes are running in a given environment.  He had gotten a lot of the information compiled, but was having a problem dissecting the Job Schedule “Frequency Interval” field.  Now, to anyone who is familiar with binary, this is a fairly straight-forward bit-flag usage of a higher-order data-type.  But, not all DBAs have this in their background, and I think many could benefit from seeing it spelled out.

In the SQL Agent, you have the ability to have week-based job schedules, in which, you can specify particular days of the week.  For example, you may have a weekly job that performs your FULL backups, that only runs on Saturday, and then have your DIFFs run on Monday, Tuesday, Wednesday, Thursday, Friday, and Sunday.  Well, SQL Agent stores this sort of information in a single field as an integer.  This may seem awkward, but the logic behind it is very straight-forward.  Each of the lowest-order 7 bits represents a yes/no flag for a particular day of the week:

SQLAgentIntervalFrequenceBinaryExplaination
So, now that we have examined what this integer really means, the question naturally follows: “How do we represent this integer as a series of days?” I have written two different pieces of code to accomplish this. The first simply takes an integer at the beginning of the script and returns a simple string containing the days represented by that integer (this could easily be turned into a UDF or stored procedure). The second piece of code goes about things a little differently, but works well for the particular use case that we were originally discussing.

The first T-SQL:

SET NOCOUNT ON;
DECLARE @numberToTest   TINYINT = 119,
		@weekString     CHAR(21) = 'SunMonTueWedThuFriSat',
		@resultString	VARCHAR(35) = ''
 
IF OBJECT_ID('TempDB..#daysOfWeek') IS NOT NULL
	DROP TABLE #daysOfWeek
 
CREATE TABLE #daysOfWeek
(
[dayNumber]   TINYINT,
[dayCode]     TINYINT
)
 
INSERT INTO
	[#daysOfWeek]
	(
	[dayNumber],
	[dayCode]
	)
VALUES
	(1,1),
	(2,2),
	(3,4),
	(4,8),
	(5,16),
	(6,32),
	(7,64)
 
SELECT
	@resultString = @resultString + CASE
						WHEN (@numberToTest & [dayCode]) = [dayCode] THEN
							SUBSTRING(@weekString,(([dayNumber] - 1) * 3 + 1),3) + ', '
						ELSE ''
					END
FROM
	[#daysOfWeek]                                    
 
-- Strip off the last comma
IF (LEN(@resultString) > 1)
	SET @resultString = LEFT(@resultString,LEN(@resultString) - 1)
 
SELECT
	@resultString

And, the second T-SQL:

SET NOCOUNT ON;
 
IF OBJECT_ID('TempDB..#daysOfWeek') IS NOT NULL
       DROP TABLE #daysOfWeek
 
CREATE TABLE #daysOfWeek
(
[dayNumber]   TINYINT,
[dayCode]     TINYINT,
[dayName]		VARCHAR(11)
)
 
INSERT INTO
       [#daysOfWeek]
       (
       [dayNumber],
       [dayCode],
	   [dayName]
       )
VALUES
       (1,1, 'Sunday '),
       (2,2, 'Monday '),
       (3,4, 'Tuesday '),
       (4,8, 'Wednesday '),
       (5,16, 'Thursday '),
       (6,32, 'Friday '),
       (7,64, 'Saturday ')
 
;WITH BaseData AS (
			SELECT
				[SYSJOB].[job_id],
				[SYSJOB].[name],
				[SYSSCH].[freq_interval],
				[DOW].[dayName]
			FROM
				[msdb].[dbo].[sysjobs] AS SYSJOB
 
					INNER JOIN [msdb].[dbo].[sysjobschedules] AS SYSJOBSCH ON
						[SYSJOB].[job_id] = [SYSJOBSCH].[job_id]
 
					INNER JOIN [msdb].[dbo].[sysschedules] AS SYSSCH ON
						[SYSJOBSCH].[schedule_id] = [SYSSCH].[schedule_id]
 
						LEFT OUTER JOIN [#daysOfWeek] AS DOW ON
							([SYSSCH].[freq_interval] & [DOW].[dayCode]) = [DOW].[dayCode]    
			)
SELECT
	[job_id],
	[name],
	REPLACE(REPLACE([DayNames],'',''),'','') AS DayNames
FROM
	(
	SELECT
		[job_id],
		[name],
		(
		SELECT
			[dayName]
		FROM
			[BaseData] 
		WHERE
			[job_id] = [Grouped].[job_id]
		FOR XML PATH ('')
		) AS DayNames
	FROM
		[BaseData] AS Grouped
	GROUP BY
		[job_id],
		[name]
	) AS ConcatenatedData

A Few Changes in SQL Server 2012 (DBCC LOGINFO and DBCC CHECKDB)

Last week I put my first SQL Server 2012 instance into production, and therefore, started applying some of my Production scripts and jobs to that server. Upon doing so, I quickly noticed that two of my jobs were getting errors, two jobs that had been solid for years on other servers, so I took a look.

DBCC LOGINFO

First, on DBCC LOGINFO, the SQL team added a single field: RecoveryUnitId. I have tried to discover what this field is for, but have had no luck as of yet. Regardless, it means that my Check VLF Density script will have to change. This is a script that I run across all of my instances occasionally, but specifically whenever we have a DR site outage, to make sure that my Transaction Logs are not getting too full (due to their SQL Mirror Failover Partners being inaccessible). I have included the script below, it is not fancy, but it gets the job done. Note: It has a threshold set to only display databases whose VLF “density” is above 60%, this is configurable at the top of the script.

-- Only shows databases with a VLF density greater than 60% (configurable via @minimumDensity)
 
DECLARE @minimumDensity INT
SET @minimumDensity = 60
 
DECLARE @databaseList TABLE
(
    [database]            VARCHAR(256),
    [executionOrder]    [int] IDENTITY(1,1) NOT NULL
)
 
DECLARE @vlfDensity    TABLE
(
    [server]        VARCHAR(256),
    [database]        VARCHAR(256),
    [density]        DECIMAL(7,2),
    [unusedVLF]        INT,
    [usedVLF]        INT,
    [totalVLF]        INT
)
 
-- Starting in SQL Server 2012, an extra column was added to LogInfo, named RecoveryUnitId
-- Need to accomodate SQL Server 2012 (version 11.0)
DECLARE @versionString            VARCHAR(20),
        @serverVersion            DECIMAL(10,5),
        @sqlServer2012Version    DECIMAL(10,5)
 
SET        @versionString    = CAST(SERVERPROPERTY('productversion') AS VARCHAR(20))
SET        @serverVersion = CAST(LEFT(@versionString,CHARINDEX('.', @versionString)) AS DECIMAL(10,5))
SET        @sqlServer2012Version = 11.0 -- SQL Server 2012
 
IF(@serverVersion >= @sqlServer2012Version)
    BEGIN
        -- Use the new version of the table  
        DECLARE @logInfoResult2012 TABLE
            (
            [RecoveryUnitId]    INT NULL,
            [FileId]            INT NULL,
            [FileSize]            BIGINT NULL,
            [StartOffset]        BIGINT NULL,
            [FSeqNo]            INT NULL,
            [Status]            INT NULL,
            [Parity]            TINYINT NULL,
            [CreateLSN]            NUMERIC(25, 0) NULL
            )
    END  
ELSE  
    BEGIN
        -- Use the old version of the table
        DECLARE @logInfoResult2008 TABLE
            (
            [FileId]            INT NULL,
            [FileSize]            BIGINT NULL,
            [StartOffset]        BIGINT NULL,
            [FSeqNo]            INT NULL,
            [Status]            INT NULL,
            [Parity]            TINYINT NULL,
            [CreateLSN]            NUMERIC(25, 0) NULL
            )
 
    END
 
DECLARE
    @currentDatabaseID    INT,
    @maxDatabaseID        INT,
    @dbName                VARCHAR(256),
    @density            DECIMAL(7,2),
    @unusedVLF            INT,
    @usedVLF            INT,
    @totalVLF            INT
 
INSERT INTO
    @databaseList
    (
    [database]
    )
SELECT
    [name]
FROM
    [sys].[sysdatabases]
 
SELECT
    @currentDatabaseID = MIN([executionOrder]),
    @maxDatabaseID = MAX([executionOrder])
FROM
    @databaseList
 
WHILE @currentDatabaseID <= @maxDatabaseID
    BEGIN
 
        SELECT
            @dbName = [database]
        FROM
            @databaseList
        WHERE
            [executionOrder] = @currentDatabaseID
 
        IF(@serverVersion >= @sqlServer2012Version)
            BEGIN
                -- Use the new version of the table 
                DELETE
                    @logInfoResult2012
                FROM
                    @logInfoResult2012
 
                INSERT INTO
                    @logInfoResult2012
                EXEC('DBCC LOGINFO([' + @dbName + '])')
 
                SELECT
                    @unusedVLF = COUNT(*)
                FROM
                    @logInfoResult2012
                WHERE
                    [Status] = 0
 
                SELECT
                    @usedVLF = COUNT(*)
                FROM
                    @logInfoResult2012
                WHERE
                    [Status] = 2
 
                SELECT
                    @totalVLF = COUNT(*)
                FROM
                    @logInfoResult2012
            END          
        ELSE
            BEGIN
                -- Use the old version of the table 
                DELETE
                    @logInfoResult2008
                FROM
                    @logInfoResult2008
 
                INSERT INTO
                    @logInfoResult2008
                EXEC('DBCC LOGINFO([' + @dbName + '])')
 
                SELECT
                    @unusedVLF = COUNT(*)
                FROM
                    @logInfoResult2008
                WHERE
                    [Status] = 0
 
                SELECT
                    @usedVLF = COUNT(*)
                FROM
                    @logInfoResult2008
                WHERE
                    [Status] = 2
 
                SELECT
                    @totalVLF = COUNT(*)
                FROM
                    @logInfoResult2008
            END      
 
        SELECT
            @density = CONVERT(DECIMAL(7,2),@usedVLF) / CONVERT(DECIMAL(7,2),@totalVLF) * 100
 
        INSERT INTO
            @vlfDensity
            (
            [server],
            [database],
            [density],
            [unusedVLF],
            [usedVLF],
            [totalVLF]
            )
        VALUES
            (
            @@SERVERNAME,
            @dbName,
            @density,
            @unusedVLF,
            @usedVLF,
            @totalVLF
            )
 
        SET @currentDatabaseID = @currentDatabaseID + 1
    END
 
SELECT
    [server],
    [database],
    [density],
    [unusedVLF],
    [usedVLF],
    [totalVLF]
FROM
    @vlfDensity
WHERE
    [density] > @minimumDensity
ORDER BY
    [density] DESC

DBCC CHECKDB

Secondly, on DBCC CHECKDB WITH TABLERESULTS, the SQL team added several columns and replaced a couple others. This required me to change a critical stored procedure that I use regularly to check those databases that, for a multitude of reasons, do not fit into my normal Backup –> Restore Elsewhere –> DBCC CHECKDB –> DELETE methodology (outlined here). This stored procedure is called by a SQL Agent Job and sends me e-mails with the results of its findings. I will also need to make this change to my other method, but I will be moving that process to a different server and will probably do that change at that time (since I cannot restore my 2012 databases to that 2008 R2 instance anyway).

Below, I have listed the stored procedure that I have on each database instance. Note: If you want to use this script, you would need to make sure you have DBMail Setup and Configured, and then supply the appropriate DBMail Profile and Recipient e-mail address(es) to the script via two variables declared at the top of the stored procedure. Furthermore, to narrow down the databases against which the procedure is ran, you can change the query that fills the @databasesToAnalyze table variable. Both of these sections are called out in comment lines at the beginning of the stored procedure.

Lastly, I would like to thank Robert Davis (Blog | Twitter) and Roji Thomas (Blog | Twitter) for helping me find a couple columns I left out, sometimes a second or third pair of eyes can really help. This is yet another reason why the SQL Twitter community (especially via the #sqlhelp hashtag) is so valuable.

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC [administrator].[usp_DBCCCheckDB_SpecialDatabases]
-- =============================================
-- Author:        Brad Hoff
-- Create date: 2010_05_23
-- Change Log:    2012_08_29 - Brad Hoff - Changing sproc to accomodate changes in SQL Server 2012 DBCC CHECKDB command
--
-- Description:    Performs DBCC CheckDB against a subset of the databases
--                in the current instance.  This is to cover the databases
--                which cannot be checked via the normal weekly offload
--                process
-- =============================================
ALTER PROCEDURE [administrator].[usp_DBCCCheckDB_SpecialDatabases]
 
AS
BEGIN
    SET NOCOUNT ON;
 
    DECLARE    @currentDatabaseIdentifier    INT,
            @maxDatabaseIdentifier        INT,
            @currentDatabaseName        VARCHAR(256),
            @instanceName                VARCHAR(100),
            @statusMsg                    VARCHAR(MAX),
            @statusCode                    BIT,
            @msgSubject                    VARCHAR(200),
            @recipients                    NVARCHAR(MAX),
            @dbMailProfile                VARCHAR(64)
 
------------------------------------------------------------
 --=======================================================---
 --========================================================--
 ------------------------------------------------------------
    SET    @recipients = 'ENTER YOUR E-MAIL ADDRESSES HERE, SEPERATED BY SEMICOLONS'
    SET    @dbMailProfile = 'ENTER YOUR DBMail PROFILE HERE, DBMAIL IS REQUIRED FOR THIS SPROC'
------------------------------------------------------------
 --=======================================================---
 --========================================================--
 ------------------------------------------------------------
    SET @instanceName = @@SERVERNAME
    SET    @statusMsg = 'DBCC CheckDB Process is starting for select special databases on ' + @instanceName + ' at ' + CONVERT(VARCHAR,GETDATE(),120) + '<BR><BR>'
    SET    @statusCode = 0
 
    -- Starting in SQL Server 2012, several columns changed for the DBCC Check DB With Table Results command
    -- Need to accomodate SQL Server 2012 (version 11.0)
    DECLARE @versionString            VARCHAR(20),
            @serverVersion            DECIMAL(10,5),
            @sqlServer2012Version    DECIMAL(10,5)
 
    SET        @versionString    = CAST(SERVERPROPERTY('productversion') AS VARCHAR(20))
    SET        @serverVersion = CAST(LEFT(@versionString,CHARINDEX('.', @versionString)) AS DECIMAL(10,5))
    SET        @sqlServer2012Version = 11.0 -- SQL Server 2012
 
    DECLARE    @databasesToAnalyze    TABLE
    (
    [identifier]    INT    IDENTITY(1,1)    NOT NULL,
    [databaseName]    VARCHAR(256)        NOT NULL
    )
 
------------------------------------------------------------
--=======================================================---
--========================================================--
------------------------------------------------------------
-- REPLACE THIS SECTION WITH WHATEVER LOGIC YOU NEED FOR YOUR ENVIRONMENT
-- BY DEFAULT, THIS SCRIPT JUST CHECKS ALL DATABASES OTHER THAN TEMPDB
 
    INSERT INTO
        @databasesToAnalyze
        (
        [databaseName]
        )
    SELECT
        [name]
    FROM
        [sys].[databases]
    WHERE
        [name] NOT IN ('tempdb')
 
 -- END DATABASE SELECTION AREA
 ------------------------------------------------------------
 --=======================================================---
 --========================================================--
 ------------------------------------------------------------
 
    IF NOT EXISTS (
                    SELECT
                        *
                    FROM
                        [sys].[tables]
                    WHERE
                        [name] = 'tbl_CheckDBResults'
                    )
        BEGIN
            IF(@serverVersion >= @sqlServer2012Version)
                BEGIN
                    CREATE TABLE [dbo].[tbl_CheckDBResults]
                    (
                    [ServerName] VARCHAR(100) NULL,
                    [DatabaseName] VARCHAR(256) NULL,
                    [Error] INT NULL,
                    [Level] INT NULL,
                    [State] INT NULL,
                    [MessageText] VARCHAR(7000) NULL,
                    [RepairLevel] INT NULL,
                    [Status] INT NULL,
                    [DbId] INT NULL,
                    [DbFragId] INT NULL,
                    [ObjectId] INT NULL,
                    [IndexId] INT NULL,
                    [PartitionId] INT NULL,
                    [AllocUnitId] INT NULL,
                    [RidDbld] INT NULL,
                    [RidPruId] INT NULL,
                    [File] INT NULL,
                    [Page] INT NULL,
                    [Slot] INT NULL,
                    [RefDBId] INT NULL,
                    [RefPruId] INT NULL,
                    [RefFile] INT NULL,
                    [RefPage] INT NULL,
                    [RefSlot] INT NULL,
                    [Allocation] INT NULL,
                    [insert_date] DATETIME NULL
                    ) ON [PRIMARY]    
                END
            ELSE
                BEGIN
                    CREATE TABLE [dbo].[tbl_CheckDBResults]
                    (
                    [ServerName] VARCHAR(100) NULL,
                    [DatabaseName] VARCHAR(256) NULL,
                    [Error] INT NULL,
                    [Level] INT NULL,
                    [State] INT NULL,
                    [MessageText] VARCHAR(7000) NULL,
                    [RepairLevel] INT NULL,
                    [Status] INT NULL,
                    [DbId] INT NULL,
                    [Id] INT NULL,
                    [IndId] INT NULL,
                    [PartitionId] INT NULL,
                    [AllocUnitId] INT NULL,
                    [File] INT NULL,
                    [Page] INT NULL,
                    [Slot] INT NULL,
                    [RefFile] INT NULL,
                    [RefPage] INT NULL,
                    [RefSlot] INT NULL,
                    [Allocation] INT NULL,
                    [insert_date] DATETIME NULL
                    ) ON [PRIMARY]    
                END    
        END
 
    -- Setup default for Insert_Date field
    IF NOT EXISTS (SELECT [name] FROM [sys].[default_constraints] WHERE [name] = 'DF_CheckDBResult_insert_date')
        ALTER TABLE [dbo].[tbl_CheckDBResults] ADD  CONSTRAINT [DF_CheckDBResult_insert_date]  DEFAULT (getdate()) FOR [insert_date]
 
    IF NOT EXISTS (
                    SELECT
                        *
                    FROM
                        [sys].[tables]
                    WHERE
                        [name] = 'tbl_CheckDBResultSummary'
                    )
        BEGIN
            IF(@serverVersion >= @sqlServer2012Version)
                BEGIN
                    CREATE TABLE [dbo].[tbl_CheckDBResultSummary]
                    (
                    [ServerName] VARCHAR(100) NULL,
                    [DatabaseName] VARCHAR(256) NULL,
                    [Error] INT NULL,
                    [Level] INT NULL,
                    [State] INT NULL,
                    [MessageText] VARCHAR(7000) NULL,
                    [RepairLevel] INT NULL,
                    [Status] INT NULL,
                    [DbId] INT NULL,
                    [DbFragId] INT NULL,
                    [ObjectId] INT NULL,
                    [IndexId] INT NULL,
                    [PartitionId] INT NULL,
                    [AllocUnitId] INT NULL,
                    [RidDbld] INT NULL,
                    [RidPruId] INT NULL,
                    [File] INT NULL,
                    [Page] INT NULL,
                    [Slot] INT NULL,
                    [RefDBId] INT NULL,
                    [RefPruId] INT NULL,
                    [RefFile] INT NULL,
                    [RefPage] INT NULL,
                    [RefSlot] INT NULL,
                    [Allocation] INT NULL,
                    [insert_date] DATETIME NULL
                    ) ON [PRIMARY]
                END
            ELSE
                BEGIN
                    CREATE TABLE [dbo].[tbl_CheckDBResultSummary]
                    (
                    [ServerName] VARCHAR(100) NULL,
                    [DatabaseName] VARCHAR(256) NULL,
                    [Error] INT NULL,
                    [Level] INT NULL,
                    [State] INT NULL,
                    [MessageText] VARCHAR(7000) NULL,
                    [RepairLevel] INT NULL,
                    [Status] INT NULL,
                    [DbId] INT NULL,
                    [Id] INT NULL,
                    [IndId] INT NULL,
                    [PartitionId] INT NULL,
                    [AllocUnitId] INT NULL,
                    [File] INT NULL,
                    [Page] INT NULL,
                    [Slot] INT NULL,
                    [RefFile] INT NULL,
                    [RefPage] INT NULL,
                    [RefSlot] INT NULL,
                    [Allocation] INT NULL,
                    [insert_date] DATETIME NULL
                    ) ON [PRIMARY]
                END
        END
 
    SELECT
        @maxDatabaseIdentifier = MAX([identifier]),
        @currentDatabaseIdentifier = MIN ([identifier])
    FROM
        @databasesToAnalyze
 
    WHILE (@currentDatabaseIdentifier <= @maxDatabaseIdentifier)
        BEGIN
            SELECT
                @currentDatabaseName = [databaseName]
            FROM
                @databasesToAnalyze
            WHERE
                [identifier] = @currentDatabaseIdentifier
 
            DELETE
                [dbo].[tbl_CheckDBResults]
            FROM
                [dbo].[tbl_CheckDBResults]
            WHERE
                    [ServerName] = @instanceName
                AND    [DatabaseName] = @currentDatabaseName        
 
            DELETE
                [dbo].[tbl_CheckDBResultSummary]
            FROM
                [dbo].[tbl_CheckDBResultSummary]
            WHERE
                    [ServerName] = @instanceName
                AND    [DatabaseName] = @currentDatabaseName
 
            -- Start DBCC Check DB
 
            IF(@serverVersion >= @sqlServer2012Version)
                BEGIN
                    EXEC('
                    INSERT INTO
                        [dbo].[tbl_CheckDBResults]
                        (
                        [Error],
                        [Level],
                        [State],
                        [MessageText],
                        [RepairLevel],
                        [Status],
                        [DbId],
                        [DbFragId],
                        [ObjectId],
                        [IndexId],
                        [PartitionId],
                        [AllocUnitId],
                        [RidDbld],
                        [RidPruId],
                        [File],
                        [Page],
                        [Slot],
                        [RefDBId],
                        [RefPruId],
                        [RefFile],
                        [RefPage],
                        [RefSlot],
                        [Allocation]
                        )
                    EXEC(''DBCC CHECKDB(''''' + @currentDatabaseName + ''''') WITH TABLERESULTS'')')
                END
            ELSE
                BEGIN
                    EXEC('
                    INSERT INTO
                        [dbo].[tbl_CheckDBResults]
                        (
                        [Error],
                        [Level],
                        [State],
                        [MessageText],
                        [RepairLevel],
                        [Status],
                        [DbId],
                        [Id],
                        [IndId],
                        [PartitionId],
                        [AllocUnitId],
                        [File],
                        [Page],
                        [Slot],
                        [RefFile],
                        [RefPage],
                        [RefSlot],
                        [Allocation]
                        )
                    EXEC(''DBCC CHECKDB(''''' + @currentDatabaseName + ''''') WITH TABLERESULTS'')')
                END
 
            UPDATE
                [dbo].[tbl_CheckDBResults]
            SET
                [ServerName] = @instanceName,
                [DatabaseName] = @currentDatabaseName
            WHERE
                [ServerName] IS NULL
 
            INSERT INTO
                [dbo].[tbl_CheckDBResultSummary]
            SELECT
                *
            FROM
                [dbo].[tbl_CheckDBResults]
            WHERE
                    [ServerName] = @instanceName
                AND    [DatabaseName] = @currentDatabaseName
                AND [MessageText] LIKE 'CHECKDB%'
 
            -- Start Analysis
            IF (@currentDatabaseName = 'master')
                BEGIN
                    IF EXISTS (
                                SELECT
                                    *
                                FROM
                                    [dbo].[tbl_CheckDBResultSummary]
                                WHERE
                                        [ServerName] = @instanceName
                                    AND    [DatabaseName] = @currentDatabaseName
                                    AND [MessageText] LIKE 'CHECKDB found 0 allocation errors and 0 consistency errors in database ''master''%'
 
                                )
                        BEGIN
                            SET    @statusMsg = @statusMsg + 'DBCC FOR ' + @currentDatabaseName + ' Passed.<BR>'
                            -- if successful, we need to update the extended property at the actual database
                            EXEC('EXEC [master].[administrator].[usp_LastDBCCCheckDB_Set] [' + @currentDatabaseName + ']')
                        END    -- Condition: A passing entry for this DB in DBCC Summary
                    ELSE IF EXISTS (
                                    SELECT
                                        [ServerName]
                                    FROM
                                        [dbo].[tbl_CheckDBResultSummary]
                                    WHERE
                                            [ServerName] = @instanceName
                                        AND    [DatabaseName] = @currentDatabaseName
                                    )
                        BEGIN
                            SET    @statusMsg = @statusMsg + 'DBCC FOR ' + @currentDatabaseName + ' Failed! (Check the tbl_CheckDBResults table)<BR>'
                            SET @statusCode = 1
                        END    -- Condition: No passing entry for this DB in DBCC Summary
                    ELSE
                        BEGIN
                            SET    @statusMsg = @statusMsg + 'DBCC FOR ' + @currentDatabaseName + ' was not properly performed (Check Configuration)<BR>'
                            SET @statusCode = 1
                        END    -- Condition: No entry whatsoever for this DB in DBCC Summary        
 
                    IF EXISTS (
                                SELECT
                                    *
                                FROM
                                    [dbo].[tbl_CheckDBResultSummary]
                                WHERE
                                        [ServerName] = @instanceName
                                    AND    [DatabaseName] = @currentDatabaseName
                                    AND [MessageText] LIKE 'CHECKDB found 0 allocation errors and 0 consistency errors in database ''mssqlsystemresource''%'
 
                                )
                        BEGIN
                            SET    @statusMsg = @statusMsg + 'DBCC FOR ' + 'mssqlsystemresource' + ' Passed.<BR>'
                            -- if successful, we need to update the extended property at the actual database
                            EXEC('EXEC [master].[administrator].[usp_LastDBCCCheckDB_Set] [' + @currentDatabaseName + ']')
                        END    -- Condition: A passing entry for this DB in DBCC Summary
                    ELSE IF EXISTS (
                                    SELECT
                                        [ServerName]
                                    FROM
                                        [dbo].[tbl_CheckDBResultSummary]
                                    WHERE
                                            [ServerName] = @instanceName
                                        AND    [DatabaseName] = @currentDatabaseName
                                    )
                        BEGIN
                            SET    @statusMsg = @statusMsg + 'DBCC FOR ' + 'mssqlsystemresource' + ' Failed! (Check the tbl_CheckDBResults table)<BR>'
                            SET @statusCode = 1
                        END    -- Condition: No passing entry for this DB in DBCC Summary
                    ELSE
                        BEGIN
                            SET    @statusMsg = @statusMsg + 'DBCC FOR ' + 'mssqlsystemresource' + ' was not properly performed (Check Configuration)<BR>'
                            SET @statusCode = 1
                        END    -- Condition: No entry whatsoever for this DB in DBCC Summary                            
                END
            ELSE
                BEGIN
                    IF EXISTS (
                                SELECT
                                    *
                                FROM
                                    [dbo].[tbl_CheckDBResultSummary]
                                WHERE
                                        [ServerName] = @instanceName
                                    AND    [DatabaseName] = @currentDatabaseName
                                    AND [MessageText] LIKE 'CHECKDB found 0 allocation errors and 0 consistency errors in database %'
                                )
                        BEGIN
                            SET    @statusMsg = @statusMsg + 'DBCC FOR ' + @currentDatabaseName + ' Passed.<BR>'
                            -- if successful, we need to update the extended property at the actual database
                            -- I am removing this for use by outside DBAs
                            -- EXEC('EXEC [master].[administrator].[usp_LastDBCCCheckDB_Set] [' + @currentDatabaseName + ']')
                        END    -- Condition: A passing entry for this DB in DBCC Summary
                    ELSE IF EXISTS (
                                    SELECT
                                        [ServerName]
                                    FROM
                                        [dbo].[tbl_CheckDBResultSummary]
                                    WHERE
                                            [ServerName] = @instanceName
                                        AND    [DatabaseName] = @currentDatabaseName
                                    )
                        BEGIN
                            SET    @statusMsg = @statusMsg + 'DBCC FOR ' + @currentDatabaseName + ' Failed! (Check the tbl_CheckDBResults table)<BR>'
                            SET @statusCode = 1
                        END    -- Condition: No passing entry for this DB in DBCC Summary
                    ELSE
                        BEGIN
                            SET    @statusMsg = @statusMsg + 'DBCC FOR ' + @currentDatabaseName + ' was not properly performed (Check Configuration)<BR>'
                            SET @statusCode = 1
                        END    -- Condition: No entry whatsoever for this DB in DBCC Summary
                END
 
            SET @currentDatabaseIdentifier = @currentDatabaseIdentifier + 1
        END
 
    SET    @statusMsg = @statusMsg + '<BR>DBCC CheckDB Process is starting for select special databases on ' + @instanceName + ' at ' + CONVERT(VARCHAR,GETDATE(),120) + '<BR>'
 
    IF @statusCode = 0
        BEGIN
            SET @msgSubject = 'SUCCESS - DBCC CheckDB for special databases on ' + @instanceName
        END    -- Condition: There were no errors or failures in the consistency checking of this instance
    ELSE
        BEGIN
            SET @msgSubject = 'FAILURE - DBCC CheckDB for special databases on ' + @instanceName
        END    -- Condition: At least one consistency check either failed or resulted in an error
 
    EXEC [msdb].[dbo].[sp_send_dbmail]
        @profile_name = @dbMailProfile, 
        @recipients = @recipients,
        @body = @statusMsg,
        @subject = @msgSubject,
        @body_format = 'HTML';
 
END

I hope these scripts and information can be a help to someone.

Fundamentals Matter

I am currently interviewing candidates for a new Junior DBA at my company.  Even though the position for which I am hiring is a junior position, I do expect the candidates to have a base level of technology, know the basics of server administration, and have some strong critical thinking skills.  So, to test the way each potential employee thinks, I ask a lot of intermediate to sometimes advanced SQL questions.  My thinking is that, 1) I want to know what level the candidate is really at, and 2) I want to discover how they respond to questions they haven’t faced before.  Have they dug down deep into the material, trying to learn as much as they could?  Have they been casual observers of technology?  Have they tried to test out ideas they’ve heard?  I certainly don’t consider my questions pass/fail, they are just an indication of where the person sitting across from me is in their technical career.

I have had several candidates in so far and a few of them have come to me via a recruiter.  After I had wrapped up with the recruiter regarding some of the candidates, I had mentioned to him that some of them, while completely qualified for this position, may want to stretch themselves a bit, because I see a lot of potential there.  He asked if i could clarify, so I started trying to put it into a concise idea.  I realized that the core of what I was trying to get across was simple, people who are starting into their technical career need to learn how to think about technology, not just learn the syntax and the base skills.  So, I came up with these suggestions, mostly specific to SQL Server, that I thought would get the gears turning about how to start digging.  These are not an end unto themselves, but simple stepping stones into building a technology paradigm; in this case, using the vehicle of SQL Server.

  1. Dig into how the syntax works.  Start by opening up SSMS and going through the different wizards.  Select different options and parameters, and then click the “Script” button at the top of the dialog to produce a script that you can read through.  Start working through it and find which parts of the syntax are new to you.  If you find a parameter you are not familiar with, or if you come across an entire group of keywords you have never used before, start researching them.  Find out what the code is actually doing.  Books Online is a great resource to start reading up on the syntax, but then expand your research and test it out for yourself.  Books Online DOES have errors in it, as do hundreds of blogs, forums, and twitter-feeds.  All of this information is coming from some human, double-check their work.
  2. Learn the first layer of Internals.  Read up on how the Transaction Log functions and the role it plays in the database.  Learn what VLFs are and how they work within the Transaction Log.  Learn the process data goes through when queried, updated, and deleted.  There is a lot going on here and understanding the basic principles will help you immensely.
  3. Start building a paradigm.  I like to think that there is no technology that we posses today that was not built up through a quasi-logical process, placing one building block on another along the way.  This enables me to build a paradigm that I can start plugging new information into.  The more robust and thorough my model is, the better I can assimilate new ideas.  If I come across something that doesn’t fit, I either have to recognize the failing in my paradigm and adjust my model to be more correct, or I have to find out why that new information is wrong.  Those are the only two options I see, either I don’t understand something as well as I need to, or the new information is not completely accurate.  It is up to me to find out which is true.  Your brain may not work the exact same way that mine does, but having some type of model in your head of how different systems interact, helps you predict functionality and troubleshoot anomalies.
  4. Start to recognize false information.  A lot of ideas get passed around the SQL community, and some of them are just straight-out wrong.  There are some DBAs who believe everything they read or hear, this leads to a lot of myths being shoveled back and forth that are not true, or are not true in all cases.  Knowing this information, or at least being familiar with it, will give you a leg-up.  Start with Paul Randal’s “Common SQL Server Myths” document.  This is a great summary of some of the information out there that is just plain wrong, and it will give you some good tools to start watching out for bad information that doesn’t fit into your paradigm.

Applying this type of thought to different areas of technology can help you start to think critically and proactively about the technologies that interest you most.  So, when I speak of fundamentals, I am not talking about features and syntax, I am speaking of the fundamentals of being a technologist.  This is a reminder that I myself need to come back to again and again, and I hope it can help someone else as well.