Category Archives: T-SQL Problem-Solving - Page 2

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

Posted by on August 30, 2012 9 comments

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) + '
 
'
    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.
'
                            -- 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)
'
                            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)
'
                            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.
'
                            -- 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)
'
                            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)
'
                            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.
'
                            -- 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)
'
                            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)
'
                            SET @statusCode = 1
                        END    -- Condition: No entry whatsoever for this DB in DBCC Summary
                END
 
            SET @currentDatabaseIdentifier = @currentDatabaseIdentifier + 1
        END
 
    SET    @statusMsg = @statusMsg + '
DBCC CheckDB Process is starting for select special databases on ' + @instanceName + ' at ' + CONVERT(VARCHAR,GETDATE(),120) + '
'
 
    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.

Monitoring Virtual Log File Density in SQL Server

Posted by on February 5, 2012 No comments

The SQL Server Transaction Log is comprised of Virtual Log Files (VLFs), which vary in size, depending on how you have grown your Transaction Log over time.  Each transaction that occurs on your SQL Server instance is logged to the Transaction Log prior to being hardened to your data files.  SQL Server will start writing to one VLF and continue to move to the next VLF as it progresses through the Transaction Log.  This is how SQL Server maintains the “C” in ACID; that is “Consistency”.  There is much more to be said with regard to the Transaction Log, but I will not go into the details in this post.

If a transaction is running for a very long time, or if a log backup has not been taken in a long while, or if a VLF cannot be cleared for any other reason (a SQL mirror might be down), the number of VLFs that are available will start to diminish.  If the Transaction Log ever runs out of available VLFs, it will either have to grow the Transaction Log, or your database will have to stop.  Due to the nature of SQL Server, the database cannot remain available to users without available space to record transactions; again, due to maintaining ACIDity.  Neither eventuality is optimal: you do not want your database to become unavailable, nor would you like your Transaction Log to grow (if you can avoid it).  When a Transaction Log grows, it must zero-out the entirety of the added diskspace (even if you have Instant File Initialization configured properly).

To avoid these sorts of scenarios, I monitor the density of my active VLFs, to try to proactively handle any situation that might arise, before it becomes a crisis.  Below, I will provide the script that I use to check each Transaction Log in the instance.

This script runs “DBCC LogInfo” against each database in the instance, records the number of VLFs, how many are active, and how many are available, then calculates the density as a percentage, and displays the results:

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
)
 
DECLARE @logInfoResult TABLE
(
      [FileId]      INT NULL,
      [FileSize]    BIGINT NULL,
      [StartOffset] BIGINT NULL,
      [FSeqNo]      INT NULL,
      [Status]      INT NULL,
      [Parity]      TINYINT NULL,
      [CreateLSN]   NUMERIC(25, 0) NULL
)
 
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
 
            DELETE
                  @logInfoResult
            FROM
                  @logInfoResult
 
            INSERT INTO
                  @logInfoResult
            EXEC('DBCC LOGINFO([' + @dbName + '])')
 
            SELECT
                  @unusedVLF = COUNT(*)
            FROM
                  @logInfoResult
            WHERE
                  [Status] = 0
 
            SELECT
                  @usedVLF = COUNT(*)
            FROM
                  @logInfoResult
            WHERE
                  [Status] = 2
 
            SELECT
                  @totalVLF = COUNT(*)
            FROM
                  @logInfoResult
 
            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
ORDER BY
      [density] DESC

I sometimes run this script across all of my instances using Red-Gate MultiScript to see how my VLF densities are doing across my entire enterprise. This is particularly useful when our DR site is down for some reason, it helps me keep track of which databases, if any, are in danger of having an impact due to VLFs being unable to clear.

Data Sanitization with Regular Expressions, Sort of

Posted by on April 28, 2011 No comments

A DBA on Twitter, using the #sqlhelp hashtag, posted about a particular problem they were having in which they wanted to sanitize some data which was presumably entered in some sort of free-form text field. Their basic idea was that they would get data like any of the following:

my id is 12345
id=12345
user name (12345)

The DBA would then want to be able to sanitize this data and come up with a string containing the simple 5-digit number (in the case presented, the id should always be 5 digits).

This is a common enough problem, one that most people working with human-input data would be familiar with. There are several ways to approach it, but some work better than others, and when you are talking about a high-load system, you want the quickest and most efficient method. In my opinion, this is a perfect opportunity to use Regular Expressions. However, T-SQL does not have native support for full-fledged Regular Expressions, which is a shame, but it does have support for a small subset of Regular Expressions, and that is all we need for this particular case.

So, first, what are we trying to accomplish? We want to take any string and look for digits, between 0 and 9. T-SQL has a function named PATINDEX that takes a subset of of regular expression patterns and an expression that evaluates to some string data type and returns an integer of the index in the string where the pattern first occurs, or zero if the pattern is not matched anywhere in the string. Combining this function with SUBSTRING should give us all of the tools we need to solve this problem.

The code to return the five-digit id as a string is as follows:

DECLARE @yourStringToTest VARCHAR(20)            -- as an example for testing
DECLARE @pattern VARCHAR(20)
SET     @yourStringToTest = 'my id is 12345'
SET     @pattern = '%[0-9]%'
SELECT
    SUBSTRING(
                @yourStringToTest,               -- Source String (for the substring)
                PATINDEX(
                            @pattern,            -- The pattern we are matching
                            @yourStringToTest    -- Source String (for the pattern matching)
                            ),
                5                                -- The number of characters we are wanting to return
                )

As you can see, this is a very simple solution, but what about if the data has some really strange artifacts? What if they have data like:

my 1st ID is 12345
requesting 3 actions, my id is 12345

This poses an additional problem, how do we get to the actual id, which is the five-digit number, but skip over the integers that occur earlier in the string. If we try the code we have above, we are going to get “1st I” and “3 act”, respectively. Clearly we need to add some additional logic to our pattern. We need to ensure that the pattern looks for 5 consecutive digits, as opposed to just the first digit we encounter. Additionally, what if we want to get any id that is at least 3 digits, but not greater than 8, for example? These are all issues that would be solved very easily with Regular Expressions, unfortunately T-SQL does not support the full set of Regular Expression syntax.

There are, however, some interesting alternatives that have been created by members of the SQL community. They involve using COM objects, so I would be wary using them in any production system, but as a one-off script you need to run, it is definitely worth a shot. You can read about this method and see the code necessary to accomplish it by checking out the post made by yoinky on this SQLTeam.com forum thread.

Additionally, you could solve the problem by creating a simple SSIS package to iterate through your source data and run a Script Component to utilize the Regular Expression pattern. I’ll work on an example of that for a future post.

I would like to see Microsoft include full support of Regular Expressions in a future release of SQL Server, but until then, at least we have PATINDEX and LIKE.