Post reply

my dbcc checkdb is getting failed

  • December 12, 2011 at 4:36 am

    #253281

    Due to high tempdb usage my dbcc checkdb is getting failed as my databsae size is 300 GB.

    What should I do?

    Thanks

  • December 12, 2011 at 5:54 am

    #1419988

    Post the output from CheckDB.

    300GB is not a large database by any measure. Get to a couple TB and it's big

    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

  • December 12, 2011 at 6:10 am

    #1420006

    Thanks,

    Should I use Physical_Only option.Is it reliable?

    Thanks

  • December 12, 2011 at 6:11 am

    #1420007

    No. Normal checkDB. Shouldn't take long on a 300GB.

    DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

    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

  • December 12, 2011 at 6:13 am

    #1420012

    I have given the tempdb mdf drive size 10 gb but it is going beyond that and failed.

    Thanks

  • December 12, 2011 at 6:15 am

    #1420015

    10GB? That's not a good idea. Not just because of CheckDB, but that's an insanely small size for TempDB on a production server for normal usage.

    Move TempDB's data file to a larger drive, retry 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

  • December 12, 2011 at 6:16 am

    #1420016

    ok

    Thanks

  • December 12, 2011 at 10:03 pm

    #1420349

    Can I partitioned the Database and then run checkdb.

    Will it get successful with same tempdb drive size?

    Thanks

  • December 13, 2011 at 1:50 am

    #1420421

    If you're running full checkDB, no.

    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

  • December 13, 2011 at 1:57 am

    #1420426

    How much tempdb size will grow how would I know?

    For future reference also I want to know when db size will be 700 Gb or more , Should I restrict the size then will I able to do the checkdb?

    Thanks

  • December 13, 2011 at 2:04 am

    #1420429

    forsqlserver (12/13/2011)

    How much tempdb size will grow how would I know?

    How long is a piece of string? You can use the CheckDB option Estimate_Only, but I recall there's a known bug that causes the estimates to be way, way too low. Besides, tempDB usage isn't just CheckDB, it's query workspace, temp tables, version store, online index builds, etc, etc.

    Generally you test out the DB under load and see how much tempDB space it uses, then add a safe margin and give it room to grow.

    Should I restrict the size then will I able to do the checkdb?

    Hang on, you're considering restricting the database size so that you can run a CheckDB with a tiny tempDB? Isn't that completely the wrong way around? The database grows according to data in it, you need to ensure that there's sufficient TempDB and other resources to handle that.

    btw, you have a small database here. 300GB is nothing these days.

    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

  • December 13, 2011 at 8:48 am

    #1420681

    Agree with Mr. Gila.

    I am running Full CheckDB on database with size of 900 GB successfully ( on weekends).

Viewing 12 posts - 1 through 11 (of 11 total)

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