Solved

DBCC checkdb hitsory

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

839 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