DBCC blocking issue

[ezecastle]

I'm trying to run DBCC Showcontig on a table and when I run this It ends up blocking itself all the time. What would cause this and what fixes it?

thanks.

[ezecastle]

It also blocks on dbcc checkdb as well. Just deadlocks with itself.

[rmiao]

What's your statement? How did you check blocking? How big is the table?

[ezecastle]

well I'm running dbcc checkdb and that blocks itself, I looked in current activity and saw spid 52 blocking spid 52 and they were both the DBCC checkdb commands.

[ezecastle]

i was able to run the dbcc showcontig but it took 3-4 times to get a result.

[nosepicker]

If you're running SP4, that's probably normal. SP4 shows if a process is page "blocking" itself - its basically waiting on itself. This behavior would've happened in SP3, but it just wouldn't have shown it. SP4 is just giving you more information than before.

[ezecastle]

Interesting, thanks for the information. I will let it run and see what happens.

[rmiao]

And dbcc does take some time, especially on big table and big db.

[ezecastle]

Hi, well i finally got the dbccs to run it took a few hours. Thanks for your help.

there are many errors in the db that I ran the dbcc's this one is kind of confusing. what does it mean and how do i fix it?
thanks for your time.

Server: Msg 8905, Level 16, State 1, Line 1
Extent (10:1721016) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

[rmiao]

What's your sql version and service pack level?

[ezecastle]

sql 2000 sp4

[ezecastle]

well I was able to fix it by doing a dbcc checkdb allow_data_loss. This fixed all of those errors, I had 532 of them now I don't I'm unsure at this point what data loss occured. Is there a good way to find this out?

[rmiao]

You can restore the db from old backup to another name, then compare with third party tools.

[ezecastle]

Great! Thanks, do you know of any tools that I can use? some that you like and or used?

thanks,

[rmiao]

RedGate has db compare tool for long time.