DBCC checkdb ... with tableresults
Because it's not suggested to run DBCC CHECKDB when the DB is active (in order not to overload the and not disturb users) - it should be good to run it when the DB is inactive, and it's possible to do it in a job and insert the results into a log table:
-- create the log table:
CREATE TABLE [dbo].[checkdbHistory](
[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 DEFAULT (GETDATE())
) ON [PRIMARY]
GO
-- this insertion csn be stored in a job:
INSERT INTO dbo.checkdbHistory
( [Error], [Level], [State], MessageText, RepairLevel,
[Status], [DbId], Id, IndId, PartitionId,
AllocUnitId, [File], Page, Slot, RefFile,
RefPage, RefSlot, Allocation)
EXEC ('dbcc checkdb(''DB_NAME'') with tableresults')
GO
In order to do this to all of the databases, run the insertion in a cursor.
http://www.mssqltips.com/sqlservertip/2325/capture-and-store-sql-server-database-integrity-history-using-dbcc-checkdb/
Since I'm dealing with different versions of MSSql (2005 to 2016) and as you know, the output is not exactly the same from a version to another, how can I learn about the output format vs each version.
ReplyDeleteSay, I'm trying to avoid holding a version of the sp per version of server.
I guess we can run
DeleteEXEC ('dbcc checkdb(''DB_NAME'') with tableresults')
on each server and see what are the results.
SAve them to temp table (one for each version), and save all to one table with the columns you need.