spaghettidba
10/6/2017 - 1:45 PM
dba_RunCheckDB.sql #blog
dba_RunCheckDB.sql #blog
-- http://spaghettidba.com/2011/11/28/email-alert-dbcc-checkdb/
-- You have a TOOLS database, don't you?
-- If not, create it: you'll thank me later.
USE TOOLS;
GO
IF NOT EXISTS( SELECT 1 FROM sys.schemas WHERE name = 'maint')
EXEC('CREATE SCHEMA maint');
GO
-- =============================================
-- Author: Gianluca Sartori - spaghettidba
-- Create date: 2011-06-30
-- Description: Runs DBCC CHECKDB on the database(s) specified
-- and returns a table result that can be used in
-- reporting and alerting.
-- =============================================
CREATE PROCEDURE [maint].[dba_runCHECKDB]
@dbName nvarchar(max) = NULL, -- Database name. If NULL, will check all databases
@PHYSICAL_ONLY bit = 0, -- Set to 1 to perform physical check only. Defaults to 0.
@allMessages bit = 0, -- Set to 1 to return all the messages generated by DBCC
-- Set to 0 to return one summary message for each database (default)
@dbmail_profile sysname = NULL, -- DBMail profile to use when sending the results
@dbmail_recipient sysname = NULL, -- DBMail recipient
@log_to_table bit = 0, -- Set to 1 to enable logging to table DBCC_CHECKDB_HISTORY
@help bit = 0
AS
BEGIN
SET NOCOUNT,
XACT_ABORT,
QUOTED_IDENTIFIER,
ANSI_NULLS,
ANSI_PADDING,
ANSI_WARNINGS,
ARITHABORT,
CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
IF @help = 1
BEGIN
SELECT *
FROM (
SELECT '@dbname nvarchar(max) = NULL','Database name.'
UNION ALL SELECT '','''Database'' checks a single database'
UNION ALL SELECT '','NULL checks all databases'
UNION ALL SELECT '','''Database1,Database2,...DatabaseN'' checks the databases in the list'
UNION ALL SELECT '','''^Database1,^Database2,...^DatabaseN'' checks all databases except the ones in the list'
UNION ALL SELECT '','Combinations of positive (not prefixed with ^) and negative (prefixed with ^) elements'
UNION ALL SELECT '','considers positive elements only. Use negative elements alone.'
UNION ALL SELECT '',''
UNION ALL SELECT '@PHYSICAL_ONLY bit = 0','If set to 1, performs CHECKDB WITH PHYSICAL_ONLY'
UNION ALL SELECT '',''
UNION ALL SELECT '@allMessages bit = 0','If set to 1, returns all the messages generated by DBCC'
UNION ALL SELECT '','If set to 0, returns a summary message for each database'
UNION ALL SELECT '',''
UNION ALL SELECT '@dbmail_profile sysname = NULL','The dbmail profile to use to send out the email.'
UNION ALL SELECT '','NULL uses the global dbmail profile'
UNION ALL SELECT '',''
UNION ALL SELECT '@dbmail_recipient sysname = NULL','The address that will receive the email.'
UNION ALL SELECT '','If set to NULL disables sending the email and returns a result set.'
UNION ALL SELECT '','If not NULL sends the email without returning a result set'
UNION ALL SELECT '',''
UNION ALL SELECT '@log_to_table bit = 0','If set to 1, stores the results in the table DBCC_CHECKDB_HISTORY.'
UNION ALL SELECT '','If the table does not exists, it will create the table for you.'
UNION ALL SELECT '','When logging to a table, a result set is not returned.'
UNION ALL SELECT '',''
UNION ALL SELECT '@help bit = 0','If set to 1 displays this help.'
) AS h (
[-----parameter------------------------------------------------------]
,[-----description----------------------------------------------------------------------------------------------------------------]
)
RETURN
END
DECLARE @version int
DECLARE @sql nvarchar(4000)
DECLARE @ErrorMessage nvarchar(2048)
DECLARE @body nvarchar(max)
DECLARE @Message nvarchar(4000)
DECLARE @Severity int
DECLARE @State int
-- determine major version: DBCC output can be different
SELECT @version = CAST(REPLACE(CAST(SERVERPROPERTY('ProductVersion') AS char(2)),'.','') AS int)
-- Clean up the @dbname parameter
-- Remove leading/trailing spaces
SET @dbname = LTRIM(RTRIM(@dbname))
-- Remove leading commas
WHILE LEFT(@dbname,1) = ','
SET @dbname = SUBSTRING(@dbname, 2, LEN(@dbname))
-- Remove trailing commas
WHILE RIGHT(@dbname,1) = ','
SET @dbname = SUBSTRING(@dbname, 1, LEN(@dbname)-1)
IF OBJECT_ID('tempdb..##DBCC_OUTPUT') IS NOT NULL
DROP TABLE ##DBCC_OUTPUT
-- SQL2005, SQL2008, SQL2008R2 produce this output
-- except for the columns explicitly marked as new in SQL 2012
CREATE TABLE ##DBCC_OUTPUT(
Error int NULL,
[Level] int NULL,
State int NULL,
MessageText nvarchar(2048) NULL,
RepairLevel nvarchar(22) NULL,
Status int NULL,
DbId int NULL, -- was smallint in SQL2005
DbFragId int NULL, -- new in SQL2012
ObjectId int NULL,
IndexId int NULL,
PartitionId bigint NULL,
AllocUnitId bigint NULL,
RidDbId smallint NULL, -- new in SQL2012
RidPruId smallint NULL, -- new in SQL2012
[File] smallint NULL,
Page int NULL,
Slot int NULL,
RefDbId smallint NULL, -- new in SQL2012
RefPruId smallint NULL, -- new in SQL2012
RefFile smallint NULL,
RefPage int NULL,
RefSlot int NULL,
Allocation smallint NULL
)
-- Add a computed column
ALTER TABLE ##DBCC_OUTPUT ADD Outcome AS
CASE
WHEN Error = 8989 AND MessageText LIKE '%0 allocation errors and 0 consistency errors%' THEN 0
WHEN Error <> 8989 THEN NULL
ELSE 1
END
-- Add an identity column to sort results when sending the email
ALTER TABLE ##DBCC_OUTPUT ADD RowId int IDENTITY(1,1)
DECLARE @localTran bit
IF @@TRANCOUNT = 0
BEGIN
SET @localTran = 1
BEGIN TRANSACTION LocalTran
END
BEGIN TRY
-- Create the history table if needed
IF @log_to_table = 1
AND OBJECT_ID('maint.DBCC_CHECKDB_HISTORY') IS NULL
BEGIN
SELECT TOP(0)
RowId,
Error,
[Level],
State,
MessageText,
RepairLevel,
Status,
DbId,
DbFragId,
CAST(NULL AS sysname) AS DatabaseName,
ObjectId,
IndexId,
PartitionId,
AllocUnitId,
RidDbId,
RidPruId,
[File],
Page,
Slot,
RefDbId,
RefPruId,
RefFile,
RefPage,
RefSlot,
Allocation,
Outcome,
GETDATE() AS RunDate
INTO maint.DBCC_CHECKDB_HISTORY
FROM ##DBCC_OUTPUT
ALTER TABLE maint.DBCC_CHECKDB_HISTORY
ADD CONSTRAINT PK_DBCC_CHECKDB_HISTORY PRIMARY KEY CLUSTERED(RowId)
END
-- Open a cursor on the matching databases (version dependant)
DECLARE c_databases CURSOR LOCAL FAST_FORWARD
FOR
SELECT QUOTENAME(name) AS name
FROM master.sys.databases
WHERE (
@dbname IS NULL
OR ',' + @dbname + ',' NOT LIKE '%,[^!^]%' ESCAPE '!'
OR ',' + @dbname + ',' LIKE '%,' + name + ',%'
OR ',' + @dbname + ',' LIKE '%,' + REPLACE(QUOTENAME(name),'[','[[]') + ',%'
)
AND ',' + ISNULL(@dbname,'') + ',' NOT LIKE '%,!^' + name + ',%' ESCAPE '!'
AND ',' + ISNULL(@dbname,'') + ',' NOT LIKE '%,!^' + REPLACE(QUOTENAME(name),'[','[[]') + ',%' ESCAPE '!'
AND state_desc = 'ONLINE'
AND is_read_only = 0
AND source_database_id IS NULL -- Exclude Snapshots
AND name <> 'tempdb'
OPEN c_databases
FETCH NEXT FROM c_databases INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
-- Build a SQL string
SET @sql = 'DBCC CHECKDB('+ @dbName +') WITH TABLERESULTS, ALL_ERRORMSGS '
IF @PHYSICAL_ONLY = 1
SET @sql = @sql + ', PHYSICAL_ONLY '
BEGIN TRY
IF @version > 8 AND @version < 11
BEGIN
-- SQL2005/2008 use this column list
INSERT INTO ##DBCC_OUTPUT (
Error,
[Level],
State,
MessageText,
RepairLevel,
Status,
DbId,
ObjectId,
IndexId,
PartitionId,
AllocUnitId,
[File],
Page,
Slot,
RefFile,
RefPage,
RefSlot,
Allocation
)
EXEC(@sql)
END
IF @version >= 11
BEGIN
-- SQL2012 uses all columns
INSERT INTO ##DBCC_OUTPUT (
Error,
[Level],
State,
MessageText,
RepairLevel,
Status,
DbId,
DbFragId,
ObjectId,
IndexId,
PartitionId,
AllocUnitId,
RidDbId,
RidPruId,
[File],
Page,
Slot,
RefDbId,
RefPruId,
RefFile,
RefPage,
RefSlot,
Allocation
)
EXEC(@sql)
END
END TRY
BEGIN CATCH
SELECT @ErrorMessage = 'Unable to run DBCC on database ' + @dbName + ': ' + ERROR_MESSAGE()
INSERT INTO ##DBCC_OUTPUT (Error, MessageText)
SELECT Error = 8989,
MessageText = @ErrorMessage
END CATCH
FETCH NEXT FROM c_databases INTO @dbName
END
CLOSE c_databases
DEALLOCATE c_databases
IF NOT EXISTS (
SELECT 1 FROM ##DBCC_OUTPUT
)
BEGIN
RAISERROR('No database matches the name specified.',10,1)
END
IF @log_to_table = 1
BEGIN
INSERT INTO maint.DBCC_CHECKDB_HISTORY (
Error,
[Level],
State,
MessageText,
RepairLevel,
Status,
DbId,
DbFragId,
DatabaseName,
ObjectId,
IndexId,
PartitionId,
AllocUnitId,
RidDbId,
RidPruId,
[File],
Page,
Slot,
RefDbId,
RefPruId,
RefFile,
RefPage,
RefSlot,
Allocation,
Outcome,
RunDate
)
SELECT
Error,
[Level],
State,
MessageText,
RepairLevel,
Status,
DbId,
DbFragId,
DatabaseName = ISNULL(DB_NAME(DbId),'resourcedb'),
ObjectId,
IndexId,
PartitionId,
AllocUnitId,
RidDbId,
RidPruId,
[File],
Page,
Slot,
RefDbId,
RefPruId,
RefFile,
RefPage,
RefSlot,
Allocation,
Outcome,
RunDate = GETDATE()
FROM ##DBCC_OUTPUT
WHERE Error = 8989
OR @allMessages = 1
OR DbId IN (
SELECT DbId
FROM ##DBCC_OUTPUT
WHERE Error = 8989
AND Outcome = 1
)
END
-- Build the final SQL statement
SET @sql =
'SELECT ISNULL(DB_NAME(DbId),''resourcedb'') AS DatabaseName, ' +
CASE @allMessages
WHEN 1 THEN '*'
ELSE 'MessageText, Outcome'
END + '
FROM ##DBCC_OUTPUT
WHERE 1 = 1 ' +
CASE @allMessages WHEN 1 THEN '' ELSE 'AND Error = 8989' END
IF @dbmail_recipient IS NULL
BEGIN
-- Query DBCC output directly
EXEC(@sql)
END
ELSE
BEGIN
-- Pipe DBCC output to a variable
SET @sql = '
SELECT @body = (
SELECT ISNULL(MessageText,'''') + char(10) AS [text()]
FROM ( ' + @sql + ' AND Error = 8989 ) AS src
WHERE Outcome = 1
ORDER BY 1 DESC
FOR XML PATH('''')
)'
EXEC sp_executesql @sql, N'@body nvarchar(max) OUTPUT', @body OUTPUT
-- Send CHECKDB report
IF @body IS NOT NULL
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @dbmail_profile,
@recipients = @dbmail_recipient,
@subject = 'Consistency error found!',
@body = @body,
@importance = 'High',
@query = '
SET NOCOUNT ON;
SELECT TOP(5000) ISNULL(DB_NAME(DbId),''resourcedb'') + '' -'' AS DatabaseName, MessageText
FROM ##DBCC_OUTPUT WITH (NOLOCK)
WHERE DbId IN (
SELECT DbId
FROM ##DBCC_OUTPUT WITH (NOLOCK)
WHERE Error = 8989
AND Outcome = 1
)
ORDER BY RowId ASC ',
@attach_query_result_as_file = 1,
@query_result_no_padding = 1,
@query_result_header = 0,
@exclude_query_output = 1,
@query_attachment_filename = 'DBCC_CHECKDB_Errors.log'
END
END
IF @localTran = 1 AND XACT_STATE() = 1
COMMIT TRAN LocalTran
IF OBJECT_ID('tempdb..##DBCC_OUTPUT') IS NOT NULL
DROP TABLE ##DBCC_OUTPUT
END TRY
BEGIN CATCH
SELECT @Message = ERROR_MESSAGE(),
@Severity = ERROR_SEVERITY(),
@State = ERROR_STATE()
IF @localTran = 1 AND XACT_STATE() <> 0
ROLLBACK TRAN
RAISERROR ( @Message, @Severity, @State)
END CATCH
END