Solved

DBCC checkdb hitsory

Posted on 2012-03-14
4
593 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 10

Assisted Solution

by:Ramesh Babu Vavilla
Ramesh Babu Vavilla earned 166 total points
ID: 37723476
0
 
LVL 10

Assisted Solution

by:Ramesh Babu Vavilla
Ramesh Babu Vavilla 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:
Scott Pletcher 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

623 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