Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

DBCC CHECKDB issue

Posted on 2011-09-06
13
Medium Priority
?
1,265 Views
Last Modified: 2012-05-12
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
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
  • 7
  • 5
13 Comments
 
LVL 19

Expert Comment

by:Rimvis
ID: 36493676
Hi sg05121983,

did you check SQL Server logs?
0
 
LVL 12

Expert Comment

by:sachitjain
ID: 36493892
Can u manually do DBCC CHECKDB (<DBName>) for databases that are being missed? It may give u some clue.
0
 

Author Comment

by:sg05121983
ID: 36494287
@Rimvis : In error log, there is no error related to integrity check.

@Sachitjain : Executed manually for some DB's, there is no error realated to DBCC CHECKDB/Integrity Check and command executed sucessfully.

 Any thoughts?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 12

Expert Comment

by:sachitjain
ID: 36494416
No idea what's going wrong with sp_Msforeachdb. But following link suggests an alternative way that should work for you.
http://weblogs.sqlteam.com/tarad/archive/2004/06/16/1606.aspx
0
 

Author Comment

by:sg05121983
ID: 36500474
Hi Sachitjain,

I have tried the script in given link, the results are same that is it is still skipping some databases and LastRunDBCCdate is not showing dbcc checkdb execution date.
0
 
LVL 12

Expert Comment

by:sachitjain
ID: 36500707
Strange!!
Pls run following query and check whether missing dbs are actually being returned by it.

SELECT TOP 1 @dbid = dbid, @DBName = name
 FROM master.dbo.sysdatabases
 WHERE dbid > @dbid
 ORDER BY dbid

Where exactly are you looking for this LastRunDBCCDate? There is one more link that could help you to find out last clean run.
http://sqlserverpedia.com/wiki/Last_clean_DBCC_CHECKDB_date
0
 

Author Comment

by:sg05121983
ID: 36502675
Hi Sachitjain,

I have executed proc dbo.GetLastRanCleanDBCCCHECKDBForAllDatabases( in above link), i got the below output:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'X20'. No entry found with that name. Make sure that the name is entered correctly.

The above mentioned DB (X20), DBCC checkDB executed sucessfully as per scheduled time. I mean this DB was not there in missing/skipping list.
0
 
LVL 12

Expert Comment

by:sachitjain
ID: 36508125
Is this db X20 coming in the list of dbs being fetched through following query

SELECT TOP 1 @dbid = dbid, @DBName = name
 FROM master.dbo.sysdatabases
 WHERE dbid > @dbid
 ORDER BY dbid

0
 

Author Comment

by:sg05121983
ID: 36510236
I got the below error:

Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@dbid".
0
 

Author Comment

by:sg05121983
ID: 36510270
declare @dbid varchar(200)
declare @dbname varchar(200)

SELECT TOP 1 @dbid = dbid, @DBName = name
 FROM master.dbo.sysdatabases
 WHERE dbid > @dbid
 ORDER BY dbid

No records from above query
0
 
LVL 12

Assisted Solution

by:sachitjain
sachitjain earned 2000 total points
ID: 36515221
Oh OK try this out

Select dbid, [name] from master.dbo.sysdatabases
If your missing dbs are not listed in the output list, follow these steps.
1.> Take manual backup of those dbs
2.> Restore them on same server instance with different name
3.> Drop existing missing dbs
4.> Rename newly restored versions with old names (that of missing and dropped in step3)

This would actually resurrect the internal configuration of your dbs if it is not correct and your dbs are not getting listed.
0
 

Accepted Solution

by:
sg05121983 earned 0 total points
ID: 36522227
Thanks Sachitjain for your help.
0
 

Author Closing Comment

by:sg05121983
ID: 36553421
--
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

618 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