Get more more handful information about corrupted database. Best practice to check database consistency and integrity on regular basis to prevent more data damage will help you to find out that something is wrong with your database. But then you need more handful information. Some job should be checking your databases for possible corruption that can be caused by several factors, for example some of them can be:
- wrong storage layer
- unexpected server failure
- abnormal SQL Server shutdown
- etc.
Once you will find that some database not passing your checks, you have to analyze it deeper. When running DBCC CHECKDB “normal way” you have to go through lines of messages returned to your SSMS. This script provides way how to make that results more SQL Server way.
INFO: Results from querying the table are the same table schema as table created during its execution
-- database will be created with default configuration
CREATE DATABASE [DBCCData]
-- change to any database you want to have DBCC output collected in
USE [DBCCData]
-- create table for data from DBCC CHECKDB command
CREATE TABLE [dbo].[DBCCResults](
[Error] [bigint] NULL,
[Level] [bigint] NULL,
[State] [bigint] NULL,
[MessageText] [varchar](7000) NULL,
[RepairLevel] [varchar](7000) NULL,
[Status] [bigint] NULL,
[DbId] [bigint] NULL,
[DbFragID] [bigint] NULL,
[ObjId] [bigint] NULL,
[IndId] [bigint] NULL,
[PartID] [bigint] NULL,
[AllocID] [bigint] NULL,
[File] [bigint] NULL,
[Page] [bigint] NULL,
[Slot] [bigint] NULL,
[RefFile] [bigint] NULL,
[RefPage] [bigint] NULL,
[RefSlot] [bigint] NULL,
[Allocation] [bigint] NULL
)
-- gather DBCC results and store them in table
INSERT INTO [DBCCData].[dbo].[DBCCResults] EXEC('DBCC CHECKDB() WITH TABLERESULTS, NO_INFOMSGS')
-- analyze collected data
SELECT * FROM [DBCCData].[dbo].[DBCCResults]
i need a column that returns 0 when check db is success and returns 1 when it has errors..how is it possible, any help?
Hello thanks for comment, can you please specify more? You want column to be added to final result set that contains flag if some errors found during DBCC CHECKDB? Am I right?