sg05121983
asked on
DBCC CHECKDB issue
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/sol
Thanks in Advance.
DBCC-History.xls
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/sol
Thanks in Advance.
DBCC-History.xls
Can u manually do DBCC CHECKDB (<DBName>) for databases that are being missed? It may give u some clue.
ASKER
@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?
@Sachitjain : Executed manually for some DB's, there is no error realated to DBCC CHECKDB/Integrity Check and command executed sucessfully.
Any thoughts?
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
http://weblogs.sqlteam.com/tarad/archive/2004/06/16/1606.aspx
ASKER
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.
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.
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
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
ASKER
Hi Sachitjain,
I have executed proc dbo.GetLastRanCleanDBCCCHE
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.
I have executed proc dbo.GetLastRanCleanDBCCCHE
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.
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
SELECT TOP 1 @dbid = dbid, @DBName = name
FROM master.dbo.sysdatabases
WHERE dbid > @dbid
ORDER BY dbid
ASKER
I got the below error:
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@dbid".
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@dbid".
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
--
did you check SQL Server logs?