Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

DBCC Hangs Expand / Collapse
Author
Message
Posted Friday, July 20, 2012 9:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, January 13, 2014 5:42 AM
Points: 226, Visits: 903
I have a 600GB DB on a SQL 2005 server that causes DBCC to stop responding when running CheckDB. This server has many other DBs on it, and DBCC checkDB is successful with those, until it gets to this one DB. DBCC never returns any kind of status or error, even with it running for 8 hours yesterday. I have databases over 1TB that run CheckDB faster.
The database is set to SQL 80 compatibility, for reasons I do not know, and Simple recovery. I inherited this. It has a single data file and a single log file. The DB server is version 9.0.3353. The database is still accessible and I have had no complaints from users about it. I'm just trying to run DBCC as part of maintenance.

Anyone know what's up with this?

Thanks,
Chris


Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Post #1333043
Posted Friday, July 20, 2012 9:19 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 42,817, Visits: 35,939
What do you mean 'hangs'? What's the wait type you see?
Enterprise edition?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1333045
Posted Friday, July 20, 2012 10:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:43 AM
Points: 6,300, Visits: 13,585
Has this database ever produced a clean CHECKDB result?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1333095
Posted Friday, July 20, 2012 11:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:34 AM
Points: 7,097, Visits: 12,601
What options are you running CHECKDB with? Can you provide the actual command issued?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1333125
Posted Friday, July 20, 2012 12:13 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, January 13, 2014 5:42 AM
Points: 226, Visits: 903
The waits I have are PageIOLatch_SH and CXPacket, on multiple instances of the command "DBCC Table Check". DM_Exec_Requests show the extact query is being: "DECLARE @BlobEater VARBINARY(8000) SELECT @BlobEater = CheckIndex(FactKeyA + FactKeyB + Facts) FROM { IRowset 0x08E1221F } GROUP BY FactKeyA >> WITH ORDER BY FactKeyA, FactKeyB OPTION(ORDER GROUP)".

This is Enterprise Edition.

When I said it hangs, I mean that if i exec it interactively I runs but never shows informational messages of what table it has checked, allocation info, like you would normally see as CheckDB works its way through DB. My automated job has switches set in the command to not show informational messages, so I don't expect them there. I noticed it ran for several hours, and the log file the job generates stopped at this DB, so I ran CheckDB interactively to see if I could see a problem.

I do not know if this DB has ever had a clean DBCC check. As I said, I inherited this and am instituting regular maintenance, which was not done by my predecessors.

Thanks,
Chris


Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Post #1333155
Posted Friday, July 20, 2012 1:59 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, January 13, 2014 5:42 AM
Points: 226, Visits: 903
The maintenance job runs:DBCC CHECKDB ([DMART_MSRIPOS]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY
(Ola Hallengren's scripts)
Interactively, I run "DBCC CheckDB(DMART_MSRIPOS)"

If I watch the wait resources, I can see that they are changing, suggesting that it is doing something, but it is going incredibly slow.
I have this in an agent job, so I'm going to let the agent run all weekend, to see if it will complete by Monday.

Thanks,
Chris


Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Post #1333217
Posted Friday, July 20, 2012 2:06 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:34 AM
Points: 7,097, Visits: 12,601
Stamey (7/20/2012)
The waits I have are PageIOLatch_SH and CXPacket, on multiple instances of the command "DBCC Table Check". DM_Exec_Requests show the extact query is being: "DECLARE @BlobEater VARBINARY(8000) SELECT @BlobEater = CheckIndex(FactKeyA + FactKeyB + Facts) FROM { IRowset 0x08E1221F } GROUP BY FactKeyA >> WITH ORDER BY FactKeyA, FactKeyB OPTION(ORDER GROUP)".

This is Enterprise Edition.

When I said it hangs, I mean that if i exec it interactively I runs but never shows informational messages of what table it has checked, allocation info, like you would normally see as CheckDB works its way through DB. My automated job has switches set in the command to not show informational messages, so I don't expect them there. I noticed it ran for several hours, and the log file the job generates stopped at this DB, so I ran CheckDB interactively to see if I could see a problem.

I do not know if this DB has ever had a clean DBCC check. As I said, I inherited this and am instituting regular maintenance, which was not done by my predecessors.

Thanks,
Chris


How to tell if data purity checks will be run?

DBCC TRACEON (3604);
GO
DBCC DBINFO ('DemoCorruptMetadata');
GO

DBINFO STRUCTURE:

DBINFO @0x6855EF64

dbi_dbid = 7 dbi_status = 16 dbi_nextid = 2089058478
dbi_dbname = DemoCorruptMetadata dbi_maxDbTimestamp = 100 dbi_version = 611
dbi_createVersion = 539 dbi_ESVersion = 0
dbi_nextseqnum = 1900-01-01 00:00:00.000 dbi_crdate = 2009-06-17 15:14:49.490
dbi_filegeneration = 0
dbi_checkptLSN

m_fSeqNo = 10 m_blockOffset = 303 m_slotId = 1
dbi_RebuildLogs = 0 dbi_dbccFlags = 0
dbi_dbccLastKnownGood = 1900-01-01 00:00:00.000
dbi_dbbackupLSN

<snip>


If dbi_dbccFlags = 1 then it has never run clean.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1333223
Posted Friday, July 20, 2012 4:18 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 42,817, Visits: 35,939
opc.three (7/20/2012)
If dbi_dbccFlags = 1 then it has never run clean.


That just tells you whether data purity checks have ever been run and return clean, not if CheckDB has. If the DB was upgraded from 2000, it's possible for that to be 0 even with a clean checkDB having been completed a second earlier.

To see when CheckDB last ran without error, look at the dbi_dbccLastKnownGood which is the date that CheckDB last ran without error. If it's 1900..., the answer is Never.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1333274
Posted Friday, July 20, 2012 4:36 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:34 AM
Points: 7,097, Visits: 12,601
GilaMonster (7/20/2012)
opc.three (7/20/2012)
If dbi_dbccFlags = 1 then it has never run clean.


That just tells you whether data purity checks have ever been run and return clean, not if CheckDB has. If the DB was upgraded from 2000, it's possible for that to be 0 even with a clean checkDB having been completed a second earlier.

To see when CheckDB last ran without error, look at the dbi_dbccLastKnownGood which is the date that CheckDB last ran without error. If it's 1900..., the answer is Never.

See Paul's comment in the article I linked to. If it is taken out of context that's ok. When I lokked for docs on that flag when I first learned of this page I came up mostly empty. I imagine that's why the poster posed the question in the first place.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1333283
Posted Friday, July 20, 2012 4:41 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 42,817, Visits: 35,939
Ok, so more complex than tht

Still, the easiest way to see when checkDB last ran cleanly is to look at the dbi_dbccLastKnownGood, which tells you exactly that, when checkDB last ran without error



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1333285
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse