Post reply

DBCC CHECKDB doing nothing

  • January 15, 2009 at 8:16 am

    #92925

    The night before I ran a DBCC CHECKDB job which does DBCC CHECKDB first and if no errors it will do the backup. Usually it takes about 4 hours (for several databases). But yesterday morning when I got to the work it is still running. I looked at Perfmom and I don't see any disk I/O. So I stopped the job thinking this must be a hiccup. Then, last night I before I leave the work, I reran that job. Again, it is still running this morning and Perfmom shows no disk I/O.

    Can someone help me why is CHECKDB hanging there doing nothing ?

    Thanks,

    Jimmy

  • January 15, 2009 at 8:22 am

    #927371

    Is it blocked by another query? Is there a wait type, and if so, what is it? (check sysprocesses)

    Are there any entries in the error log? That sometimes happens when CheckDB finds something wrong and has to go back and do a deep dive to get the details of what's wrong

    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

  • January 15, 2009 at 8:44 am

    #927415

    There was no blocking and no errors in errorlog and event viewer. I stopped the job.

    Right now, I'm running it manually from Query Analyzer. From Perfmom, I see there is only processor activity but no disk activity.

    Thanks for getting back to me. I really appreciate it.

    Jimmy

  • January 15, 2009 at 9:04 am

    #927438

    CHECKDB now only using 0.5 to 0.8% of disk activity. There is no disk activity at all. Is it hanging ?

    There is no blocking and the lastwaittype = 'PAGEIOLATCH_SH'.

    FYI, this database is 150GB running SQL Server 2000 SP4 and Windows 2003 SP2.

  • January 16, 2009 at 5:16 pm

    #928598

    Is their any error message in error log?

    MJ

  • January 21, 2009 at 7:52 am

    #930349

    Thank you for getting back to me.

    What's interesting is that there were no errors in errorlog nor in event viewer.

    Since DBCC CHECKDB hangs, I decided to run DBCC CHECKTABLE one by one until I come across to the table that hangs (it doesn't show any errors too). Then, I drop the PK and all the indexes and rebuilt them (suggested by another user from SQLTeam.com). After the rebuilt, I reran CHECKTABLE, CHECKDB and both completed successfully without errors.

    Happy New Year,

    Jimmy

  • January 21, 2009 at 12:51 pm

    #930637

    My guess is that it found a possible index inconsistency in that table. (row in cluster that's not in the noncluster or vis versa)

    The way the checkDB algorithm works is that it can easily and quickly tell that there is a problem with the table, but it won't know with which row. If it has to go back and find that info, that's what's called a deep dive, and it's very time consuming on big tables.

    The index rebuild would have fixed that problem.

    On SQL 2005, CheckDB writes a message in the error log when it encounters that condition. It does not, unfortunately, log anything on SQL 2000.

    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

  • January 22, 2009 at 7:46 am

    #931176

    Thank you for your help.

    Currently we are planning migration from SQL2000 directly to SQL2008. One of our pain is how are we going to develop and deploy SSIS packages. I mean should we use sourcesafe as central deposit, or use central SSIS server to manage all the packages. Then, when we deploy, should we use file system or SQL Server. All these just become so unclear as none of us are expert in this area. Is there a best practice for SSIS management ?

    Thanks,

    Jimmy

  • January 22, 2009 at 7:58 am

    #931183

    Please post new questions in a new thread (in an appropriate forum) as people may miss it at the end of an existing thread, and SSIS people may well not look at a thread on checkDB.

    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

  • January 22, 2009 at 8:07 am

    #931192

    Thank you...I will post it to SSIS forurm.

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

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