February 25, 2008 at 11:21 am
Hey Stanislas,
Thanks for information and as suspected, I/O (e.g. tempdb size and Raid) is likely candidate. In addition, the number of disk in array is probably low in comparision to DBCC CheckDB I/O requirements. How many disk make up your array(s)? In short-term, you may want to work in stages to ensure database integrity by targetting tables in phases with less I/O requirements. Ensure tempdb database size is incresed on system as soon as possible.
Hope this helps,
Phillip Cox
MCITP - Database Administrator
February 25, 2008 at 11:32 am
It's really more to do with the number of data files in the database being checked - the way I wrote the read-ahead algorithm in CHECKDB was based on files rather than underlying disks - SQL Server has no idea about the physical drives comprising the volumes.
I wrote a long blog post on all the factors that will contribute to CHECKDB running slowly - see http://www.sqlskills.com/blogs/paul/2007/11/15/CHECKDBFromEveryAngleHowLongWillCHECKDBTakeToRun.aspx
Hope this helps
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
February 25, 2008 at 11:44 am
Hi Paul and Phillip,
When I say it "failed", I meant that the server completely hanged, we couldn't connect to it or even ping it. Everytime this happened, the server needed a physical reboot.
tempdb is set to AutoGrow and Disk size is not a problem (about 60 Gb of free space at anytime). There are 4 arrays in the disk and 5 different databases checked in one run of the SSIS package.
I'll check your blog post Paul and see if there is something I missed somewhere.
Thanks !
Stanislas Biron
February 25, 2008 at 11:46 am
Hj all
I have given up running DBCC CHECKDB on the production server, If I run it on a 32bit version it completes in about 15 - 18 mins.
The results of the DBCC CHECKDB with ESTIMATEONLY are:-
Estimated TEMPDB space needed for CHECKALLOC (KB)
-------------------------------------------------
3640
(1 row(s) affected)
Estimated TEMPDB space needed for CHECKTABLES (KB)
--------------------------------------------------
528091
(1 row(s) affected)
The disk array that the DB's are on is a RAID 10 array with 151Gb Free.
The Temp DB is set to Autogrow by 10% unrestricted.
I could run dbcc checktable but I have over 100 tables is there a switch that will make it check all tables?
Regards
Sean
February 25, 2008 at 11:54 am
Sean - no, the switch is called DBCC CHECKDB 🙂 - it does a CHECKALLOC and then batches of CHECKTABLES in parallel. Sounds like its the 64bit limitation on Standard that's biting you 🙁
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
February 25, 2008 at 12:14 pm
Paul,
Yes I think it is something with the 64bit version, it's very frustrating, you get a large box throw RAM and Disc at it and then are held back by a limitation in SQL 2005 SE 64bit. I have a case open with PSS and will post any info I get from them.
Sean
February 25, 2008 at 12:38 pm
Hi,
Have database(s) been upgraded from SQL Server 2000 or native to SQL Server 2005?
Is this the first time running CheckDB on impacted database(s)?
Does tempdb live with database files?
What time does process run?
Paul, great article, thanks!
Phillip Cox
MCITP - Database Administrator
February 25, 2008 at 12:51 pm
Hi Phillip,
The Databases were originally SQL 6.5 databases and have been upgraded to each version as it hase come along, there was originally 25 subscribers to this main publisher, over the yars we have been able to reduce the number of subscribers and we now only have 9.
TempDB is on the same raid 10 array as the other DB.
If you mean dbcc checkdb when you say the process we have left it running for over 4 hours and when we do this SQL stops accepting any other connections, have tried just creating a new ODBC connection and that fails even telnetting to 1433 fails.
Regards
Sean
February 25, 2008 at 12:58 pm
Hi Phillip,
Yes the databases have been upgraded from SQL Server 2000 (32 bits) to SQL Server 2005 (64 bits)
This is not the first time that DBCC CHECKDB runs and it runs fine about 3 times out of 4 right now.
The tempdb live on the same RAID5 array as our databases (and it looks like that it may be the issue i'm having).
The process run at night when there is absolutely no other activity on the server.
I changed the initial size of tempdb to 1 Gig, so, at least, it won't need to autogrow during the DBCC CHECKDB process. I don't think that it will resolve the issue but it can't hurt..
Thanks everyone !
Stanislas Biron
February 25, 2008 at 1:05 pm
Sean - having tempdb on the same array as the user DBs is going to be a problem, and not just for CHECKDB. For CHECKDB, it's going to be reading and writing to tempdb at the same time as reading from the user db - basically causing disk head contention and longer average seek times for IOs as the disk heads move back and forth between sections of the disks in the array.
Do you have any large indexed views in the database? That could also be a cause of major tempdb activity due to the new checks I put in to validate indexed view contents (basically recreating the view in tempdb and doing a couple of left-anti-semi joins between the new view and old - lot's of IO there for large indexed views)
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
February 25, 2008 at 1:12 pm
Paul,
Thanks for that, I could move tempdb to the Raid 0 array that the OS lives on but that still wouldn't explain why it is only on the 64bit platform that it takes so long, on the 32 bit platform I also have the tempdb on the same disc as the production db and it isn't evan a Raid stripe it is just an IDE disc!
As it is now 8:10 pm in the UK I think I will just kick back with a glass of wine and forget about it until tomorrow!
Regards
Sean
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply