Solved

DBCC checkdb hitsory

Posted on 2012-03-14
4
574 Views
Last Modified: 2012-04-10
Hi All,

We have aroud 150 prod servers ad in each server 30-60 user DB's are der.

we have to capture DBCC CHECKDB history on DB wise(it inculdes servername,dbname,lastdbccrundate, duration).

kindly guide me how to achive above goal using tsql command.

Instead of executing each nd evry DB, need to execute script on master DB and this script will capture all DB's DBCC checkdb history on particular instance.

Thanks in Advance.
0
Comment
Question by:sg05121983
  • 2
4 Comments
 
LVL 10

Assisted Solution

by:sqlservr
sqlservr earned 166 total points
ID: 37723476
0
 
LVL 10

Assisted Solution

by:sqlservr
sqlservr earned 166 total points
ID: 37723483
http://www.sanssql.com/2011/03/t-sql-query-to-find-date-when-was-dbcc.html


CREATE TABLE #DBInfo (
       Id INT IDENTITY(1,1),
       ParentObject VARCHAR(255),
       [Object] VARCHAR(255),
       Field VARCHAR(255),
       [Value] VARCHAR(255)
)

CREATE TABLE #Value(
DatabaseName VARCHAR(255),
LastDBCCCHeckDB_RunDate VARCHAR(255)
)

EXECUTE SP_MSFOREACHDB'INSERT INTO #DBInfo Execute (''DBCC DBINFO ( ''''?'''') WITH TABLERESULTS'');
INSERT INTO #Value (DatabaseName) SELECT [Value] FROM #DBInfo WHERE Field IN (''dbi_dbname'');
UPDATE #Value SET LastDBCCCHeckDB_RunDate=(SELECT TOP 1 [Value] FROM #DBInfo WHERE Field IN (''dbi_dbccLastKnownGood'')) where LastDBCCCHeckDB_RunDate is NULL;
TRUNCATE TABLE #DBInfo';

SELECT * FROM #Value

DROP TABLE #DBInfo
DROP TABLE #Value


----this one is simple and goood
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 84 total points
ID: 37725110
That will only show you the last *successful* check, and I don't think it shows the duration.

The command below should show you all the DBCC CHECKDB commands that have run on the server **and that are still on the SQL log file**.


EXEC xp_ReadErrorLog 0, 1, 'dbcc', 'checkdb'


The first number is the relative log# to search:
0 = current log
1 = archived log #1
2 = archived log #2
etc., up to the total number of archive logs you've specified on that server.

Naturally you could refine this further by INSERTing the results of the commands into a table and using SQL to process that table:

INSERT INTO ...
EXEC xp_ReadErrorLog 0, 1, 'dbcc', 'checkdb'
0
 

Author Closing Comment

by:sg05121983
ID: 37830998
--
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now