Home Forums SQL Server 2008 SQL Server Newbies DBCC is running from 5 days RE: DBCC is running from 5 days

  • March 24, 2017 at 10:19 pm

    #1934920

    Sue_H - Friday, March 24, 2017 3:23 PM

    SQLRNNR - Friday, March 24, 2017 2:39 PM

    Sue_H - Friday, March 24, 2017 2:30 PM

    coolchaitu - Friday, March 24, 2017 12:38 AM

    We have a huge database in TB size.We have a  job that reorganizes/rebuilds indexes on all dbs and then does DBCC checkdb on all databases in the instance. It is running for last 5 days. Currently it is in suspended state with command DBCC. Waittype is PAGEIOLATCH_SH.There is no blocking and io_stall values are high. Please help in fixing this

     If that's not feasible, you could look at splitting apart the DBCC CHECKDB process and run the individual pieces - CHECKCATALOG, CHECKTABLE, CHECKALLOC, etc and run those during different times.

    Unfortunately, that does not perform a complete checkdb. While it does get the main components, there are other checks that would be skipped if this is all that was done. For that reason, the checkdb statement is still recommended to be run on a routine basis in addition to the individual pieces you have mentioned if the process is broken down.

    I'd go with Paul Randal's suggestions for VLDB since he wrote most of CHECKDB:
    CHECKDB From Every Angle: Consistency Checking Options for a VLDB

    Sue

    Yup he did write most of CheckDB. That said, some important notes from the article are found in the comments:
    1. Paul always recommends a full checkdb on a weekly basis at minimum with the exception being extenuating circumstances.
    2. Jonathan has a great article concerning the performance of checkdb and an article linked in the comments (pertains largely to vldb and it should be read).
    3. Read the linked article by Argenis about an extenuating circumstance in his vldb and sparse columns with indexes.

    And since we don't truly know the version of SQL Server the OP is using, we need to take into consideration what will be missed if a checkdb is not performed in favor of the broken up approach such as:
    SPATIAL INDEX
    XML INDEX
    Query Store
    ColumnStore
    Filestream checks
    Service Broker

    These are some of the reasons I am sure Paul still recommends a full weekly checkdb.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events