I have server called Prod-1(SQL SERVER 2005) and this server contains around 78 databases. I am using below script to run database integrity check against all databases in the instance.
EXEC sp_Msforeachdb "DBCC checkdb ('?') with physical_only"
The Job schedule details are as follows (I have scheduled job through SQL Agent) :
Occurs every week on Monday, Tuesday, Wednesday, Thursday, Friday at 10:00:00 PM EST.
Using below script, I am checking HISTORY OF CHECKDB (LASTRUNCHECKDB DATE).
CREATE TABLE #temp (
Id INT IDENTITY(1,1),
INSERT INTO #temp
EXECUTE SP_MSFOREACHDB'DBCC DBINFO ( ''?'') WITH TABLERESULTS';
;WITH CHECKDB1 AS
SELECT [Value],ROW_NUMBER() OVER (ORDER BY ID) AS rn1 FROM #temp WHERE Field IN ('dbi_dbname'))
,CHECKDB2 AS ( SELECT [Value], ROW_NUMBER() OVER (ORDER BY ID) AS rn2 FROM #temp WHERE Field IN ('dbi_dbccLastKnownGood')
SELECT CHECKDB1.Value AS DatabaseName
, CHECKDB2.Value AS LastRanDBCCCHECKDB
FROM CHECKDB1 JOIN CHECKDB2
ON rn1 =rn2
DROP TABLE #temp
As per output (PFA file - DBCC CHECKDB HISTORY), the job is not performing integrity check on all 78 databases. It was MISSING/SKIPPING some databases integrity check.
Alternatively i have modified job using below script by removing PHYSICAL_ONLY option, still it was skipping some databases.
EXEC sp_Msforeachdb "DBCC checkdb ('?')
I have searched in net, but i didn't find any solutions for my issue (why it was missing/skipping some databases integrity check).
Please share your experience/suggestions/solutions on my above issue.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
-Mike Kapnisakis, Warner Bros
With your subscription - you'll gain access to our exclusive IT community of thousands of IT pros. You'll also be able to connect with highly specified Experts to get personalized solutions to your troubleshooting & research questions. It’s like crowd-sourced consulting.
We can't always guarantee that the perfect solution to your specific problem will be waiting for you. If you ask your own question - our Certified Experts will team up with you to help you get the answers you need.
Our certified Experts are CTOs, CISOs, and Technical Architects who answer questions, write articles, and produce videos on Experts Exchange. 99% of them have full time tech jobs - they volunteer their time to help other people in the technology industry learn and succeed.
We can't guarantee quick solutions - Experts Exchange isn't a help desk. We're a community of IT professionals committed to sharing knowledge. Our experts volunteer their time to help other people in the technology industry learn and succeed.