Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cjp
Yak Posting Veteran
69 Posts |
 Posted - 2011-12-20 : 04:31:17
|
| I am running sqls 2008 64-bit on Windows 7 Pro 64-bit.I have been reading a thread in which Gail Shaw offers some guidance to someone who is having problems getting checkdb to run successfully. I have a question about this but think it belongs in a separate post. I have my tempdb set up so that it is on a separate physical drive from everything else but I am conscious that my (medical) databases are growing in size the whole time, and we have now reached a point where the largest tables are potentially larger than the maximum possible size for the tempdb.tempdb is set so that it has a starting size of about 100GB, and this can then grow to about 250 GB (the size of the hard disk on which it is located). My largest database, when indexed, would be nearer 400 GB - but there are several databases (diagnoses, prescriptions, tests) where, technically, the ultimate size of the database could exceed the maximum size of the tempdb.In normal operation, a query will use indexes to materialise only small subsets of the large databases (eg all antibiotic prescriptions or all diagnoses of heart disease between dates x and y) so the size of an extracted table is always going to be less than the size of tempdb.However, is there a requirement that tempdb needs to be at least as large as the largest database on the server if checkdb is to run properly? - I am worried that tempdb might not run properly or/and that it might deliver false positives.Thanks.Chris |
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-12-20 : 05:04:53
|
| Yes checkdb will need to use tempdb.So it should be large enough.Not sure whether larger than your database.Is there downtime acceptable for your system.If yes then you can restart sql server so that tempdb gets recreated again and then run checkdb if no then you first use estimateonly option for checkdb to understand the space requirement in tempdb for running checkdb.PBUH |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-12-20 : 11:04:46
|
| No, it doesn't need to be that large (though saying how large it does need to be is like describing the length of an arbitrary piece of string)CheckDB uses TempDB for two main things. - Fact tables that describe what it has already seen. CheckDB is broken into batches to try and keep this usage of TempDB down. The minimum size of a batch is one table, so it's likely (not 100% certain) that the amount of space used by a CheckTable of your largest table is your threshold for this.- Re-materialisation of indexed views. There are probably a bunch of other smaller things as well.I recall there's a bug with Estimate_Only that causes it to estimate too low. Can't recall the details, google should be able to turn something up.To give you an idea, I have a server I'm working with at the moment with a 100GB main database and a 7GB TempDB (though I don't know if that 7GB is from CheckDB or other usage). This will differ for different databases, so it's just an idea.--Gail ShawSQL Server MVP |
|
|
|
cjp
Yak Posting Veteran
69 Posts |
Posted - 2011-12-20 : 12:03:07
|
| Thank you both for your help. I have been having odd (and apparently random) failures when running checkdb in SQLS 2008 - sometimes it passes a table and sometimes it fails the same table. I posted about this a while back and Gail (quite rightly) said to get the hardware checked. 3 months later (I mean it...) the manufacturer is still trying (and failing) to resolve the issue. The current conversation (apparently) is with Intel regarding incompatibilities between their system board and the Adaptec RAID controller - my brain is turning to jelly.I have found that verified backups are restoring perfectly under SQLS 2005 but occasionally throw errors under 2008 - this is what underlies my question about checkdb and the size of tempdb. I am inclining to the view that it must be some sort of arcane hardware conflict.I will experiment by moving tempdb to a terabyte hard disk and see if this helps.Chris |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-12-20 : 13:02:07
|
| Have a look here:http://support.microsoft.com/kb/2634571This notes that if the entire set of tables is done in one batch (as opposed to the split up that I described), then TempDB may reach 5% of the size of the user DB. So, 5%, not 200%. Of course, that's an estimate, but it's probably a reasonable one.What specifically are those errors?--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
checkdb