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.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 DBCC CHECDB, CHECKTABLE, NEWALLOC

Author  Topic 

jdouvlos
Starting Member

11 Posts

Posted - 2002-04-04 : 15:15:19
I know the canned "books online" answer for what these bad boys do to tables (fixing indexes, checking pages etc) but can anyone who is a sql pro of 10 years or more elaborate more on them?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-04 : 16:11:04
What specifically do you want to know about them that ISN'T detailed in Books Online?

Considering that these commands change from one release to the next, 10 years of experience won't necessarily mean anything regarding how they behave in the version you're using now (by the way, which version ARE you using now?) Doubtless these commands will work in a completely different manner when Yukon is released; and they will function differently depending on whether you use the Longhorn file system or NTFS.

NEWALLOC is an obsolete command, CHECKALLOC replaces it. It checks allocation chains in the database and optionally corrects any broken links. CHECKTABLE does the same thing, but only on text/ntext and image column allocations. CHECKDB does all of these tests. They're main use is preventive maintenance, to ensure that the tables and indexes don't become so corrupted that they can't be repaired later.

Running these on a regular basis is especially critical on highly transactional databases, because INSERT and UPDATE operations can cause a lot of page splits, and page chains can get out of whack. Depending on how your disk subsytem handles caching, especially write caching, this condition can be aggravated. Clustered indexes that get rebuilt regularly help to alleviate some of this because the data is physically reordered and the allocation chains are re-written, especially if the fillfactor is altered.

You should also take a look at DBCC DBREINDEX and DBCC CHECKCATALOG (you really should have some general familiarity with all of the DBCC commands), and make sure you know the ins and outs of indexes, clustered/nonclustered, fillfactors, etc. These all intertwine with one another, and understanding them fully will help you decide the best way of using them.

Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2002-04-04 : 23:30:34
Well,

I was going to answer this post, but since I don't meet the minimum requirements, I won't

-Chad

Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-05 : 08:35:32
Neither do I

Go to Top of Page

izaltsman
A custom title

1139 Posts

Posted - 2002-04-05 : 09:51:17
Chad, please please PLEASE answer it!!!
I'd love to hear about any undocumeted aspects of these DBCC commands!!!

---------------
Strong SQL Developer wanted in the Boston area. Please e-mail if interested.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-05 : 09:56:02
Yeah! ME TOO! I wanna know if I got it right, or if I'm just talking out of my ass, as usual!

Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2002-04-05 : 12:44:39
Actually, I was just kiding, but since you want me to answer it I will. I am in a training class right now, but I will get back to this probably this weekend, or on Monday.

-Chad

Go to Top of Page

jdouvlos
Starting Member

11 Posts

Posted - 2002-04-05 : 13:10:58
I didn't want to offend anyone about the 10 years experience remark. I have 5 years experience myself and although I have a somewhat decent conceptual understanding I want to know more about what's actually happenings underneath. Also robvolk your actually wrong about the newalloc checkalloc its the other way around. "Introduced in SQL Server 6.0, NEWALLOC is the improved version of the CHECKALLOC command. NEWALLOC provides greater detail than CHECKALLOC and continues to process the remainder of the database after an error has been deleted (unlike CHECKALLOC, which stops processing when an error has been detected)." CHECKALLOC is obsolete not NEWALLOC. I've been on many websites and read alot of documentation and all I'm curious about is just to have a little more depth on what's going on when your run these commands and not just rely on the "canned" microshaft answer.

Go to Top of Page

Jay99

468 Posts

Posted - 2002-04-05 : 13:32:13
first . . .
quote:

Important DBCC NEWALLOC is identical to DBCC CHECKALLOC and is included in this Microsoft® SQL Server™ version 7.0 for backward compatibility only. It is recommended that DBCC CHECKALLOC be used to check the allocation and use of all pages in the specified database. In a future version of Microsoft SQL Server, DBCC NEWALLOC may not be supported.

--SQL 7 books on line


. . . I think Rob was right.

Second, I am curious what you are going to do with this information about how DBCC commands actually work? Why does it matter, other than what effect they have? Can you explain exactly what happens when you do a SELECT?


Jay
<O>
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-05 : 13:38:26
Hmmmmm, I based the NEWALLOC-obsolescence on SQL Books Online 2000, which Jay posted. You're using 6.5, correct? If that's the case, then follow what your version of Books Online says about these commands, as the behavior has been changed in 7.0 and upwards. I've got about 2 years experience with 6.5, but I haven't used it in almost a year now, so the ins-and-outs of that version have faded a bit from memory.

Go to Top of Page

jdouvlos
Starting Member

11 Posts

Posted - 2002-04-05 : 16:58:27
correct Mr. robvolk. I have several SQL 2000 and 7.0 servers along with "unfortunately" 2 sql 6.5 servers. Thats where I cut and pasted the documentation on 6.5 and the newalloc/checkalloc thing....We are both right. "Jay99" why are you online if you can't add anything to the forum? The answer was directed to robvolk and it was cleared up based on the documentation for 6.5, 7.0 and 2000. It's annoying and I've seen this all my tech life...people making comments and adding bit and bytes but not saying anything interesting. This is not a gripeline...serious tech prof's are trying to enhance their knowledge by sharing info. By the way I do know what a SELECT does because I understand RELATIONAL ALGEBRA and CALCULUS completely. I've read CJ DATE's books several times cover to cover.

Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-05 : 17:23:00
quote:
I've read CJ DATE's books several times cover to cover.

And you're still using a SQL database product??? Wow, Chris must be slipping! He must've gotten laid after The Third Manifesto. Don't tell him I use SQL products please, I don't want him coming to my house and shooting me.



Go to Top of Page

jdouvlos
Starting Member

11 Posts

Posted - 2002-04-05 : 17:52:08

Ah yes MSSQL I know it well. We do have a large Oracle implementation but we have vendors that require MSSQL. MSSQL is not so bad...it
gets the job done and at 1/5 the cost. I still have 2 6.5 servers that I want to upgrade to SQL2K but I get blank faces from the vendors when I ask them what up? I cant beat them over the head enough. Maybe this was not a good post....I know all about extents, pages and what DBCC does in general, I was just wondering if anyone had some deeper insights.

Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-05 : 18:10:25
I can't add anything to what you've probably seen already, you've been using the product longer than I have. I was blessed with a well-behaved and lightly used SQL Server 6.5 box (never more than 50 simultaneous users), and never experienced any problems that required a CHECKDB repair. Ran DBREINDEX maybe once a month, and it probably didn't even need it that often.

If you don't already have it, and you'll be sticking with 6.5 for a while, get Inside SQL Server 6.5 by Ron Soukup/Microsoft Press. It is INDISPENSIBLE. You can certainly find it for <$20 these days. Ron has a lot in common with Ken Henderson; you feel like you've had a brain transplant after you read the book! And the only thing in that book that isn't relevant today are devices, that's it. Every bit of knowledge and advice he gives is unbelieveably good.

I love SQL 6.5! Granted, 7.0 and 2000 are big improvements, but 6.5 has everything I really NEED in a database. I love smacking down people who gripe about varchar(8000) being too small! If only they knew...

And DON'T get me started on Clipper again...

Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2002-04-05 : 21:14:22
I second Rob's suggestion about Ron's book Inside SQL 6.5.

Okay, here we go (The 6.5 answer):
DBCC CHECKALLOC
NEWALLOC should be used in 6.5 as it will detect more errors that CHECKALLOC will.

What it does: (This is to the best of my knowledge, and has changed since 7.0)
Traverses the allocation pages in the database. It does some logging (Setting and clearing bits) as to which extents and pages it has processed and determines whether what is in the allocation pages matches what is actually allocated in the database.

Then goes through sysindexes and makes sure the index pages are allocated properly.

Then (if NOINDEX is not used) it checks all of the index pages. I believe it logs which pages have text data, and skips those. NEWALLOC will scan text/image.

CHECKDB:
This one is easy. In 6.5 it just runs a DBCC CHECKTABLE for each table in sysobjects in nthe order of the ID column.

CHECKTABLE:
Command checks the specified table to make sure data and index pages are linked properly. It confirms indexes are in proper sorted order.
It makes sure all links are consistent. It also checks some internal values and offsets to make sure things look correct.

DBCC CHECKTABLE uses multiple threads to check non-clustered indexes in parallel.

You can specify an index id and CHECKTABLE will only check the specified index.

You can use DBCC CHECKTABLE on syslogs to report the log's used and free space if the log is on a device by itself.

HTH, I doubt I have told you anything you didn't already know.

-Chad

Go to Top of Page

jdouvlos
Starting Member

11 Posts

Posted - 2002-04-06 : 09:13:33
Thanks CHAD, I knew most of that except for checktable using multiple threads....didn't know that. I guess thats all there is and "we" are all good with this stuff. I will pick up that book rob, at least until I migrate to sql 2k.

Go to Top of Page

Jay99

468 Posts

Posted - 2002-04-08 : 09:24:00
quote:

"Jay99" why are you online if you can't add anything to the forum?

It's annoying and I've seen this all my tech life...people making comments and adding bit and bytes but not saying anything interesting.



After nearly two years and helping countless people who have come here with question ranging from the completely idiotic to the absolutely fascinating, I stagger backwards as I read this. After 450+ posts, even if only 10% of them added value and/or where interesting, that is still much more value added than many people here.

This thread is riddled with misunderstanding, mostly on my part. At first glance, I though jdouvlos was looking for information about how these dbcc functions where implemented. My initial though was why does he want to know this? My eyes process color. They do it pretty well, as far as I can tell. They are the only tools I have for performing such a task. Knowing how they actually do it, doesn’t help me fix them (their not broken) and doesn’t allow me to make a choice about which tool to use (I only have 1 set of eyes). I certainly didn’t mean to question jdouvlos’s SQL prowess, only to ask, what peeks your curiosity.

Robvolk, chadmat and over 100 viewers have continued on with the thread, as if to affirm jdouvlos’s assessment of my value. In my opinion, jdouvlos comes here with: 1) a post that contained inaccuracies…”CHECKALLOC is obsolete not NEWALLOC”…and 2) poses a question that I suppose can only be answered by internal Microsoft documentation…why would anyone here have that?…and 3) regardless of how poorly my post was worded, is very judgmental and insulting. I am baffled as to how within 5 posts he is welcomed into the SQLTeam community and allowed to flame me.

I can only FigUre that my perCeption of many things (this post, this forum, my value…) is somehow sKewed. As a result, I sadlY apologize to jdOUvlos and Anyone else here that I have insuLted and step away from this forum indefiniteLy.
Go to Top of Page

jdouvlos
Starting Member

11 Posts

Posted - 2002-04-08 : 10:36:03
forget it. I apologize. I should have realized that you have added a lot to these forums before I replied to your post. Yes I've seen your
posts in perusing the forums this past weekend. Again I apologize. My judgement was misdirected. All to often I've been in forums that many treat like chat rooms.

Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-04-08 : 10:53:35
the only chat around here relates to beer...and you won't have far to go for that!!!!


everything else is honest to goodness advice from people who mean (and do) well.....



Somehow this site has enough flame-retardent material in it to avoid burn-out....thanks to the general observance of Netiquette....observe, listen, help yourself, then ask...and don't shout....

mind you we have had the odd Vesuvius in the past!!!!....

like all fires....starve an arguement of oxygen and it'll fizzle out.

Go to Top of Page

Nazim
A custom title

1408 Posts

Posted - 2002-04-08 : 11:17:29
Oh! no not you Jay. All of us know that you are capable of defending yourself . and can react to jdouvlos sharply then anyone of us can . we not condemning it or passing any remarks on his assesment isnt actually confirming it , on the contarary we hardly know the guy and thought it isnt worth passing any comments too.

About welcoming, everyone is welcomed. but you are more welcomed then others [:)). when you came back on your posting ways , the whole forums had lightened up with good humour. and we appreciate each of your posts. we wouldnt want to lose a good resource like you.

Jay this isnt the first time , the members have not reacted. there has been lot of mud thrown on rob too. and he takes care of it . I remember Chad had gone into a arguemnt with helena , we didnt rush to defend chad (though he was right). because we knew he could handle her.

Anywayz, the point is Unpack your Bag and come back to the posting ways . dont Run away ,Fight it out. and the jdouvlus apologizes too.



quote:

Robvolk, chadmat and over 100 viewers have continued on with the thread, as if to affirm jdouvlos’s assessment of my value. I am baffled as to how within 5 posts he is welcomed into the SQLTeam community and allowed to flame me.




I want to ask you something jay, if tommorow someone like this happens in your personal front will you run away or fight out for your rights. i think the later is better.
quote:

I can only FigUre that my perCeption of many things (this post, this forum, my value…) is somehow sKewed. As a result, I sadlY apologize to jdOUvlos and Anyone else here that I have insuLted and step away from this forum indefiniteLy.




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


Edited by - Nazim on 04/09/2002 00:42:48
Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2002-04-08 : 12:31:17
Jay,

I answered this post more for Ilya and Rob, than the original author, because they asked me to.

As Nazim remebers, I have been able to get into enough flame wars on my own merit, and I have determined it best to just ignore ignorant comments such as the one directed at you.

I am sorry if I offended you by continuing the thread. I think this forum is greatly enhanced by your presence and it would be a great loss for us if you were not to return.

-Chad

Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -