Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Unanswered: Integrity check on selected tables

    We do a general DB integrity check weekly through a DB maintenance plan on our SQL 2000 S.E. servers. I'd like to do a nightly integrity check on just a few tables on very large databases. The DB Maintenance Plan Wizard does not appear to allow this.

    What T-SQL can be used to accomplish this?
    Does Enterprise Manager offer a way to do this?

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: Integrity check on selected tables

    See DBCC CHECKTABLE in BOL

  3. #3
    Join Date
    Sep 2003
    Posts
    3

    Re: Integrity check on selected tables

    [See DBCC CHECKTABLE in BOL [/SIZE][/QUOTE]

    I have checked into this in the past. However, it doesn't appear to allow me to list a group of tables to check. I get a parameter incorrect for this statement.

    I'd like to do something where I list several tables. Or, all tables between the letters A and D. In this manner, I may be able to run 7 days of maintenace weekly but do integrity checking piecemeal.

  4. #4
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: Integrity check on selected tables

    Originally posted by Fulvio Hayes
    We do a general DB integrity check weekly through a DB maintenance plan on our SQL 2000 S.E. servers. I'd like to do a nightly integrity check on just a few tables on very large databases. The DB Maintenance Plan Wizard does not appear to allow this.

    What T-SQL can be used to accomplish this?
    Does Enterprise Manager offer a way to do this?
    You should be able to to create a maint plan that just does integrity checks. On my SQL7 I can go through and click off the backup portions and just turn on the nightly integrity checks.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  5. #5
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: Integrity check on selected tables

    Originally posted by Fulvio Hayes
    [See DBCC CHECKTABLE in BOL
    I have checked into this in the past. However, it doesn't appear to allow me to list a group of tables to check. I get a parameter incorrect for this statement.

    I'd like to do something where I list several tables. Or, all tables between the letters A and D. In this manner, I may be able to run 7 days of maintenace weekly but do integrity checking piecemeal. [/SIZE][/QUOTE]

    Create sp:

    dbcc checktable for table from list (get list from table will be better)

    dbcc checktable 'tableA'
    dbcc checktable 'tableB'
    ......................
    Also, you can save results of checking in table or return as recordset.

    insert #tmp
    dbcc checktable 'tableA'
    insert #tmp
    dbcc checktable 'tableB'

    select * from #tmp

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Load a list of tables into a cursor dataset, and then loop through the set to execute your DBCC.

    If you store the name of the last table completed, you can start with the next table the following night. You could even define a processing period by setting your code to exit the loop after a certain number of minutes, or at a specified hour.

    blindman

  7. #7
    Join Date
    Sep 2003
    Posts
    3
    Originally posted by blindman [/i]
    Load a list of tables into a cursor dataset, and then loop through the set to execute your DBCC.

    If you store the name of the last table completed, you can start with the next table the following night. You could even define a processing period by setting your code to exit the loop after a certain number of minutes, or at a specified hour.

    blindman

    Thanks! I'll try that. In some cases, I may use 'snails' recommendation to use checktable repeatedly for a small number of recurring tables. But for my larger, high I/O databases I'll look to going the route of the cursor dataset you recommend. I'll let youknow how it works.

    Fulvio

Posting Permissions

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