troubleshooting Question

DBCC CHECKDB issue

Avatar of sg05121983
sg05121983 asked on
Microsoft SQL Server 2005Microsoft SQL Server 2008
13 Comments2 Solutions1363 ViewsLast Modified:
Hi,

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),
       ParentObject VARCHAR(255),
       [Object] VARCHAR(255),
       Field VARCHAR(255),
       [Value] VARCHAR(255)
)

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.

Thanks in Advance.


DBCC-History.xls
ASKER CERTIFIED SOLUTION
sg05121983

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 2 Answers and 13 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 2 Answers and 13 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004