July 20, 2012 at 9:15 am
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.
July 20, 2012 at 9:19 am
What do you mean 'hangs'? What's the wait type you see?
Enterprise edition?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 20, 2012 at 10:36 am
Has this database ever produced a clean CHECKDB result?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 20, 2012 at 11:18 am
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
July 20, 2012 at 12:13 pm
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.
July 20, 2012 at 1:59 pm
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.
July 20, 2012 at 2:06 pm
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
July 20, 2012 at 4:18 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 20, 2012 at 4:36 pm
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
July 20, 2012 at 4:41 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 21, 2012 at 1:17 am
You should only run checkdb once with data_purity on a pre 2005 database to invoke the flag, this is not necessary on 2005 on as the checks are automatically completed.
Try running checkdb with physical_only and see if you get a clean result.
Also can you restore a copy to a test server and run the checks there.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 23, 2012 at 5:16 am
I left it to run all weekend and it turns out it only ran 4:41.00. Is CheckDB cumulative, in that whatever it did before is saved and it continues where it left off? I ask because it ran for 8 hours the previous day with no conclusion or results, which caused me to create this thread. Seems strange it would run in less time on the subsequent attempt.
Anyway, it found no problems and will run on a regular basis in the future.
Thanks for your help on this.
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
July 23, 2012 at 5:51 am
Stamey (7/23/2012)
Is CheckDB cumulative, in that whatever it did before is saved and it continues where it left off?
No.
I ask because it ran for 8 hours the previous day with no conclusion or results, which caused me to create this thread. Seems strange it would run in less time on the subsequent attempt.
IO load, CPU load. CheckDB uses a lot of both, if there's concurrent activity it's likely to run slower (and so's everything else)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2012 at 6:12 am
Stamey (7/23/2012)
I left it to run all weekend and it turns out it only ran 4:41.00. Is CheckDB cumulative, in that whatever it did before is saved and it continues where it left off? I ask because it ran for 8 hours the previous day with no conclusion or results, which caused me to create this thread. Seems strange it would run in less time on the subsequent attempt.Anyway, it found no problems and will run on a regular basis in the future.
Thanks for your help on this.
Chris
did you run it with PHYSICAL_ONLY or your original query selection?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 23, 2012 at 7:50 am
Also, if you ran it with DATA_PURITY through Ola's scripts last week but without it over the weekend that could explain the difference in time.
PS as long as it had never been run...what were the results of checking the boot page contents?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply