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/sol
utions on my above issue.
Thanks in Advance.