Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136

    Unanswered: DBCC CHECKDB maintenance Job

    Hi everyone,
    I want to run a DBCC CHECKDB maintenance Job once a week. SQL Server 2005 SP2 64-bit, about 37 user dbs, about 800GB of data.

    Q1:
    I am considering using ‘NOINDEX’ because I am also planning on re-organizing or re-building all my indexes the following night, and I want to try and save some time. Is this save or should I allow DBCC CHECKDB to check the non-clustered indexes?

    Q2:
    What does DBCC CHECKDB do to the database’s tlog? Will it bloat my tlogs? Should I shrinkfile right after I checkdb?

    Q3:
    I will be running the job during off hours (midnight to 4am) as to not have locking issues with clients. Any other considerations?

    Thanks

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Q1 - don't bother with checking them, since you're gonna be dropping/rebuilding them the following night.

    Q2 - doesn't do anything to tlog of the db you check, just hits tempdb for any scrap space.

    Q3 - make sure you back it up before the job...If the job fails and you end up with some sort of corruption, - you already have a backup device that contains the problem, especially if you need to involve MS.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •