Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DBCC checkdb hitsory

Posted on 2012-03-14
4
Medium Priority
?
605 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 498 total points
ID: 37723476
0
 
LVL 10

Assisted Solution

by:Ramesh Babu Vavilla
Ramesh Babu Vavilla earned 498 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 70

Accepted Solution

by:
Scott Pletcher earned 252 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

721 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