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 |
