Solved

DBCC checkdb hitsory

Posted on 2012-03-14
4
575 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

910 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

22 Experts available now in Live!

Get 1:1 Help Now