DBCC CHECKDB: 2+ hours?

Question

Post reply

DBCC CHECKDB: 2+ hours?

Jesse Reich

Hall of Fame

Points: 3020
More actions
June 29, 2010 at 9:52 am

#236986

I have a SQL Server 2008 Express database we are preparing for deployment. There is one table with 6m+ rows and takes up about 1.5GB. This table will continue to grow and we have an archiving process to maintain the DB size < 4GB.
The rest of the tables are relatively small. DBCC CHECKDB is taking 2-3 hours to complete.
I'm running these tests on an Intel Core 2 Duo 4GB RAM 150GB HD but the time for DBCC CHECKDB to complete is same on our production server.
Is this a normal time for this size or do I have some kind of configuration issue? The large table will be getting inserts 24/7 so I'm concerned about performance.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

It depends on many factors...

Have a look this: http://msdn.microsoft.com/en-us/library/ms175515.aspx

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

sql_lock SSCrazy Eights Points: 9373 More actions · Answer 2

As prev post suggest in the link, it is possible that you are hitting an I/O issue.

Jesse Reich Hall of Fame Points: 3020 More actions · Answer 3

This is way beyond my level of expertise...that said, unless there are any other suggestions I'll start with PerfMon using the recommendations from Brent Ozar's blog post: http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Are there any entries in the log? Long duration for checkDB can be a sign that it's encountered a potential or actual corruption.

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

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

Jesse Reich Hall of Fame Points: 3020 More actions · Answer 5

Yes, apparently there are issues.

Some quick background. The original database is running on a demo / test server that is receiving data from a live feed.

Yesterday I was running DBCC CHECKDB on the demo server, but it had already gone over 2 hours and so I cancelled it (was that my first mistake?). I took a backup of the database and copied it to my laptop.

I ran DBCC CHECKDB last night on my laptop and there were no errors. It took approximately 2.5 hours to run. That's when I posted the question about time.

This morning I restarted SQL Server on the demo machine (second mistake?). The reason I did this was because I wanted to see how fast the tempdb was growing in a DBCC CHECKDB. I saw the responses on the posting and ran a PerfMon check against the server while running DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS

DBCC CHECKDB finished on the demo server with errors.

Msg 8921, Level 16, State 1, Line 1

Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.

Msg 701, Level 17, State 123, Line 1

There is insufficient system memory in resource pool 'internal' to run this query.

An the error log shows this:

2010-06-29 10:10:36.20 spid54 Error: 701, Severity: 17, State: 123.

2010-06-29 10:10:36.20 spid54 There is insufficient system memory in resource pool 'internal' to run this query.

2010-06-29 10:10:36.42 spid54 DBCC CHECKDB (GPS_DB) WITH all_errormsgs, no_infomsgs executed by COMPUTER\8KB terminated abnormally due to error state 5. Elapsed time: 0 hours 33 minutes 21 seconds.

So to recap:

1) DBCC CHECKDB of restored backup on my laptop: ok

2) DBCC CHECKDB of database on server: fail

3) This is a test / demo environment. System is not in production yet so we can rebuild from scratch if necessary.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

How much memory on the server? How much allocated to SQL?

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

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

Jesse Reich Hall of Fame Points: 3020 More actions · Answer 7

The server has 4GB.

For memory allocation, I looked in Server Properties > Memory. The Maximum server memory is 2147483646 MB.

FYI, this is SQL Server 2008 Express. So I assume our limitation is 1GB.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

*

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

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

Jesse Reich Hall of Fame Points: 3020 More actions · Answer 9

Update:

I've been able to duplicate the error. I created a new database from scratch and ran DBCC CHECKDB with no problems. I exported all the data from the original DB via bcp, and imported it into the new one. When I ran DBCC CHECKDB again, I got the same errors:

Error: 701, Severity: 17, State: 123.

There is insufficient system memory in resource pool 'internal' to run this query.

I dropped the large table, reloaded it (only with 2 million rows), ran DBCC CHECKDB, and it passed.

Loaded 2 more million rows and it passed.

Loaded 2 more million rows and it failed.

Deleted 1 million rows and it passed.

For the *fun* of it, I also created a 4GB database in Express and ran DBCC CHECKDB. There were no problems.

Sigh...going to be long night.

Update 2:

Read many blog posts about DBCC CHECKDB.

Increased virtual paging file from 2GB to 4GB.

Attempted DBCC CHECKTABLE on the main table with TABLOCK

Same error.