DBCC CHECKDB to a table
This table MUST be created in your admin database BEFORE you can run this procedure!!!! If the SQL version is 2008 or below, you need to create this version of the table...
CREATE TABLE dba.dbcc_history
(
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
, TimeStamp DATETIME
NULL
CONSTRAINT DF_dbcc_history_TimeStamp DEFAULT ( GETDATE() ) -- defaults to current date/time of when written to.
)
ON
[PRIMARY];
GO If the SQL version is 2012 and above, you need to create this version of the table...
CREATE TABLE dba.dbcc_history_2012
(
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
, RidDbId 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
, Timestamp DATETIME
NULL
CONSTRAINT DF_dbcc_history_2012_TimeStamp DEFAULT ( GETDATE() ) -- defaults to current date/time of when written to.
)
ON
[PRIMARY];
GO
Once either of the above has been created in your DBA admin database (depending upon version of SQL Server), you can then create the stored procedure, once you have replaced all environment specific labels. You can then either run this on an adhoc basis, for example:
exec [dba].[DBA_RUN_CHECKDB_LOGGED] 'DBAdmin'
, or schedule this to run as an out of core hours job, given the overhead that running a CHECKDB has on a system. (CPU, IO, TEMPDB etc.) You can then query the previously created tables thus.
For SQL 2012 and above
select DB_NAME(DbId), * from dbadmin.dba.dbcc_history_2012 where repairlevel is not NULL
--where MessageText like '%error%'
For SQL 2012 and below
Select DB_NAME(DbId), * from dbadmin.dba.dbcc_history where repairlevel is not NULL
--where MessageText like '%error%'
looking for error messages or repair levels that might need your attention
--############################################################################################################################
--
--This script is being offered for public use and as such is being offered as untested and unverified.
--Please use this script at your own risk, as I take NO responsibility for it's use elsewhere in environments
--that are NOT under my control.
--Redistribution or sale of dba_indexDefrag_sp, in whole or in part, is prohibited!
--Always ensure that you run such scripts in test prior to production and perform due diligence as to whether they meet yours,
--or your company needs!
--
--############################################################################################################################
USE [your admin database here]
GO
/****** Object: StoredProcedure [dba].[DBA_RUN_CHECKDB_LOGGED] Script Date: 04/07/2016 09:49:22 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [your admin schema here].[DBA_RUN_CHECKDB_LOGGED]
--#############################################################################
--
-- Author: Haden Kingsland
-- Date: 15/10/2015
-- Version: 01:00
--
-- Desc: To run a DBCC CHECKDB across all online databases. This has been written as the original, below process
-- had issues when running the sp_MSforeachdb procedure and was missing some databases!
--
--EXEC sp_MSforeachdb N'IF DATABASEPROPERTYEX(''?'', ''Collation'') IS NOT NULL
--
--BEGIN
--
--DBCC CHECKDB (?) WITH ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY
--
--END' ;
--
-- Usage:
--
-- exec [dbadmin].[dba].[DBA_RUN_CHECKDB_LOGGED] '' -- 'DBAdmin'
--
-- Modification History
-- ====================
--
-- 07/03/2016 -- Haden KingslandFirst cut of the procedure written to run DBCC CHECKDB and output results to the
--dbcc_history (or dbcc_history_2012) table in the DBAdmin database
--
-- 15/03/2016 -- Haden Kingsland To overcome the below error seen when running DBCC CHECKDB against databases with a hyphon in the name
--when using the exec @sql syntax!
--
--Msg 102, Level 15, State 1, Line 1
--Incorrect syntax near '-'.
--Msg 319, Level 15, State 1, Line 1
--Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause
--or a change tracking context clause, the previous statement must be terminated with a semicolon.
--
--07/04/2016 -- Haden KingslandChanged the reference to @@VERSION to make it more efficient in working out what version of SQL it is running under
--after issues with the wildcard options.
--
-- References -- https://www.mssqltips.com/sqlservertip/2325/capture-and-store-sql-server-database-integrity-history-using-dbcc-checkdb/
--
--##########################################################################################################################
--
-- This table MUST be created in your admin database BEFORE you can run this procedure!!!!
--
-- If the SQL version is 2008 or below, you need to create this version of the table...
--
--CREATE TABLE [dba].[dbcc_history](
--[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,
--[TimeStamp] [datetime] NULL CONSTRAINT [DF_dbcc_history_TimeStamp] DEFAULT (GETDATE()) -- defaults to current date/time of when written to.
--) ON [PRIMARY]
--GO
-- If the SQL version is 2012 and above, you need to create this version of the table...
--
--create table [dba].[dbcc_history_2012]
--(
-- [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,
-- [RidDbId] 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,
-- [Timestamp] datetime NULL CONSTRAINT [DF_dbcc_history_2012_TimeStamp] DEFAULT (GETDATE()) -- defaults to current date/time of when written to.
--) on [PRIMARY];
--
--truncate table [dba].[dbcc_history]
--
--##########################################################################################################################
@database_name SYSNAME=''
AS
BEGIN
DECLARE
--@database_name varchar(200),
@database_id bigint,
@MESSAGE_BODY nvarchar(250),
@MESSAGE_BODY2 nvarchar(250),
@MESSAGE_BODY3 nvarchar(250),
@MESSAGE_BODY4 nvarchar(500),
@MESSAGE_BODY5 nvarchar(500),
@MESSAGE_SUBJECT varchar(200),
@command nvarchar(500),
@ERR_MESSAGE varchar(400),
@ERR_NUM bigint,
@XPCMDSH_ORIG_ON varchar(1),
@MailProfileName VARCHAR(50),
@SQL varchar(500),
@ver varchar(15);
-------------
-- DEBUG...
-------------
--declare @database_name SYSNAME
--set @database_name = 'DBAdmin'
SELECT @MailProfileName = name
FROM msdb.dbo.sysmail_profile WITH (NOLOCK)
WHERE name like '%<your profile here>%'
---------------------
-- Initialize variables
---------------------
set @XPCMDSH_ORIG_ON = ''
set @SQL = ''
set @ver = ''
SELECT @ver = CASE WHEN @@VERSION LIKE '% - 9.0%' THEN 'SQL 2005'
WHEN @@VERSION LIKE '% - 8.0%'THEN 'SQL 2000'
WHEN @@VERSION LIKE '% - 10.0%' THEN 'SQL 2008'
WHEN @@VERSION LIKE '% - 10.5%' THEN 'SQL 2008 R2'
WHEN @@VERSION LIKE '% - 11.0%' THEN 'SQL 2012'
WHEN @@VERSION LIKE '% - 12.0%' THEN 'SQL 2014'
END;
--select @@VERSION
--select @ver
--------------------------------------------------------------------------------------------------------------------
-- Check whether xp_cmdshell is turned off via Surface Area Configuration (2005) / Instance Facets (>2008)
-- This is best practice !!!!! If it is already turned on, LEAVE it on !!
-- turn on advanced options
EXEC sp_configure 'show advanced options', 1 reconfigure
RECONFIGURE
CREATE TABLE #advance_opt (name VARCHAR(20),min int, max int, conf int, run int)
INSERT #advance_opt
EXEC sp_configure 'xp_cmdshell' -- this will show whether xp_cmdshell is turned on or not
IF (select conf from #advance_opt) = 0 -- check if xp_cmdshell is turned on or off, if off, then turn it on
BEGIN
set @XPCMDSH_ORIG_ON = 'N' -- make a note that it is NOT supposed to be on all the time
--turn on xp_cmdshell to allow operating system commands to be run
EXEC sp_configure 'xp_cmdshell', 1 reconfigure
RECONFIGURE
END
ELSE
BEGIN
-- make a note that xp_cmdshell was already turned on, so not to turn it off later by mistake
set @XPCMDSH_ORIG_ON = 'Y'
END
-- drop the temporary table to tidy up after ourselves.
IF EXISTS (
select * from tempdb.sys.objects
where name like '%advance_opt%'
)
BEGIN
drop table #advance_opt
END
--------------------------------------------------------------------------------------------------------------------
IF @database_name = '' -- Then run the procedure against all databases
BEGIN
DECLARE check_databases CURSOR FOR
select
name,
database_id
from sys.databases
where database_id NOT IN (3) -- ignore the model database
and state in (0,4) -- online or suspect mode databases only
and is_read_only = 0
and source_database_id IS NULL -- only real database, so no database snapshots!
order by name;
-- Open the cursor.
OPEN check_databases;
-- Loop through the update_stats cursor.
FETCH NEXT
FROM check_databases
INTO @database_name, @database_id;
WHILE @@FETCH_STATUS <> -1 -- Stop when the FETCH statement failed or the row is beyond the result set
BEGIN
IF @@FETCH_STATUS = 0 -- to ignore -2 status "The row fetched is missing"
BEGIN
BEGIN TRY
-- DEBUG
--declare @sql varchar(500)
--declare @database_name sysname
--declare @ver varchar(15)
--set @ver = 'SQL 2014'
--set @database_name = 'DBAdmin'
set @sql = 'DBCC CHECKDB ' + '(' + '[' + @database_name + ']' + ')' + ' WITH ALL_ERRORMSGS, DATA_PURITY, tableresults'
--print @sql;
--exec (@sql);
IF LTRIM(RTRIM(@ver)) not in ('SQL 2012','SQL 2014') -- before version 2012 and 2014
BEGIN
INSERT INTO [your admin database here].[your admin schema here].dba.dbcc_history ([Error], [Level], [State], MessageText, RepairLevel, [Status],
[DbId], Id, IndId, PartitionId, AllocUnitId, [File], Page, Slot, RefFile, RefPage,
RefSlot,Allocation) exec(@sql)
END;
ELSE
BEGIN -- for versions 2012 and 2014 only
INSERT INTO [your admin database here].[your admin schema here].dbcc_history_2012
([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 @ERR_MESSAGE = ERROR_MESSAGE(), @ERR_NUM = ERROR_NUMBER();
SET @MESSAGE_BODY='Failure running the DBCC CHECKDB LOGGED job for ' + @database_name + ' Error Message = ' + @ERR_MESSAGE
SET @MESSAGE_SUBJECT='Failure running the DBCC CHECKDB LOGGED job for ' + @database_name + ' on ' + @@SERVERNAME
EXEC msdb.dbo.sp_notify_operator
@profile_name = @MailProfileName,
@name=N'Haden Kingsland',
@subject = @MESSAGE_SUBJECT,
@body= @MESSAGE_BODY;
print @MESSAGE_BODY
print @MESSAGE_SUBJECT
END CATCH
--FETCH NEXT FROM check_databases INTO @database_name, @database_id;
END -- end of @@fetchstatus if
FETCH NEXT FROM check_databases INTO @database_name, @database_id;
END
-- Close and deallocate the cursor.
CLOSE check_databases;
DEALLOCATE check_databases;
END
ELSE -- run against a specified database (ie: exec [dba].[DBA_RUN_CHECKDB_LOGGED] 'DB Name Here'
set @sql = 'DBCC CHECKDB ' + '(' + '[' + @database_name + ']' + ')' + ' WITH ALL_ERRORMSGS, DATA_PURITY, tableresults'
select @ver
BEGIN
IF LTRIM(RTRIM(@ver)) not in ('SQL 2012','SQL 2014') -- before version 2012 and 2014
BEGIN
INSERT INTO [your admin database here].[your admin schema here].dbcc_history ([Error], [Level], [State], MessageText, RepairLevel, [Status],
[DbId], Id, IndId, PartitionId, AllocUnitId, [File], Page, Slot, RefFile, RefPage,
RefSlot,Allocation) exec(@sql)
END
ELSE
BEGIN -- for versions 2012 and 2014 only
INSERT INTO [your admin database here].[your admin schema here].dbcc_history_2012
([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
--INSERT INTO dbadmin.dba.dbcc_history ([Error], [Level], [State], MessageText, RepairLevel, [Status],
--[DbId], Id, IndId, PartitionId, AllocUnitId, [File], Page, Slot, RefFile, RefPage, RefSlot,Allocation)
--EXEC ('DBCC CHECKDB ' + '(' + @database_name + ')' + ' WITH ALL_ERRORMSGS, DATA_PURITY, tableresults')
END
-----------------------------------------------------------------------------------------------------------------------
-- turn off advanced options
IF @XPCMDSH_ORIG_ON = 'N' -- if xp_cmdshell was NOT originally turned on, then turn it off
BEGIN
-- turn on advanced options again just to ensure we can turn off xp_cmdshell!
EXEC sp_configure 'show advanced options', 1 reconfigure
RECONFIGURE
-- turn off xp_cmdshell to dis-allow operating system commands to be run
EXEC sp_configure 'xp_cmdshell', 0 reconfigure
RECONFIGURE
EXEC sp_configure 'show advanced options', 0 reconfigure
RECONFIGURE
END
-----------------------------------------------------------------------------------------------------------------------
END
--exec [dba].[DBA_RUN_CHECKDB_LOGGED] 'DBAdmin' 
