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?See DBCC CHECKTABLE in BOL|||[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.|||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.|||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|||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|||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

Integrity check failures during maintenance plans

Hi
I have maintenance plans which run weekly to check the integrity of all my
databases. I have the option 'repair minor errors' turned on and I then
recieve the following error when the maintenenace plan runs. I have placed
the database in question into single user mode and run the same task on the
database and it completes successfully. The problem is though that I can run
the integrity check with the 'repair minor errors' option turned off and it
does not pick up any errors.
Any advise on solving this issue would be great?
Check Data and Index Linkage
Error Number: 7919
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.Don't use the repair option. If you have a problem in the database, you want to know about it,
analyze why it happened, make sure you repair the faulty hardware (for example) etc. In short, that
option isn't a good idea to use in an automated job. If you still want to use that option, you
either have to add something to the job that kicks out all users, or live with that error if there
are users connected.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DeanM" <DeanM@.discussions.microsoft.com> wrote in message
news:AFC39BF6-9706-489A-83B5-BF7F7AD366CA@.microsoft.com...
> Hi
> I have maintenance plans which run weekly to check the integrity of all my
> databases. I have the option 'repair minor errors' turned on and I then
> recieve the following error when the maintenenace plan runs. I have placed
> the database in question into single user mode and run the same task on the
> database and it completes successfully. The problem is though that I can run
> the integrity check with the 'repair minor errors' option turned off and it
> does not pick up any errors.
> Any advise on solving this issue would be great?
>
> Check Data and Index Linkage
> Error Number: 7919
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.

Integrity check failures during maintenance plans

Hi
I have maintenance plans which run weekly to check the integrity of all my
databases. I have the option 'repair minor errors' turned on and I then
recieve the following error when the maintenenace plan runs. I have placed
the database in question into single user mode and run the same task on the
database and it completes successfully. The problem is though that I can ru
n
the integrity check with the 'repair minor errors' option turned off and it
does not pick up any errors.
Any advise on solving this issue would be great?
Check Data and Index Linkage
Error Number: 7919
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement
not
processed. Database needs to be in single user mode.Don't use the repair option. If you have a problem in the database, you want
to know about it,
analyze why it happened, make sure you repair the faulty hardware (for examp
le) etc. In short, that
option isn't a good idea to use in an automated job. If you still want to us
e that option, you
either have to add something to the job that kicks out all users, or live wi
th that error if there
are users connected.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DeanM" <DeanM@.discussions.microsoft.com> wrote in message
news:AFC39BF6-9706-489A-83B5-BF7F7AD366CA@.microsoft.com...
> Hi
> I have maintenance plans which run weekly to check the integrity of all my
> databases. I have the option 'repair minor errors' turned on and I then
> recieve the following error when the maintenenace plan runs. I have place
d
> the database in question into single user mode and run the same task on th
e
> database and it completes successfully. The problem is though that I can
run
> the integrity check with the 'repair minor errors' option turned off and i
t
> does not pick up any errors.
> Any advise on solving this issue would be great?
>
> Check Data and Index Linkage
> Error Number: 7919
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statemen
t not
> processed. Database needs to be in single user mode.

Integrity check failures during maintenance plans

Hi
I have maintenance plans which run weekly to check the integrity of all my
databases. I have the option 'repair minor errors' turned on and I then
recieve the following error when the maintenenace plan runs. I have placed
the database in question into single user mode and run the same task on the
database and it completes successfully. The problem is though that I can run
the integrity check with the 'repair minor errors' option turned off and it
does not pick up any errors.
Any advise on solving this issue would be great?
Check Data and Index Linkage
Error Number: 7919
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
Don't use the repair option. If you have a problem in the database, you want to know about it,
analyze why it happened, make sure you repair the faulty hardware (for example) etc. In short, that
option isn't a good idea to use in an automated job. If you still want to use that option, you
either have to add something to the job that kicks out all users, or live with that error if there
are users connected.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DeanM" <DeanM@.discussions.microsoft.com> wrote in message
news:AFC39BF6-9706-489A-83B5-BF7F7AD366CA@.microsoft.com...
> Hi
> I have maintenance plans which run weekly to check the integrity of all my
> databases. I have the option 'repair minor errors' turned on and I then
> recieve the following error when the maintenenace plan runs. I have placed
> the database in question into single user mode and run the same task on the
> database and it completes successfully. The problem is though that I can run
> the integrity check with the 'repair minor errors' option turned off and it
> does not pick up any errors.
> Any advise on solving this issue would be great?
>
> Check Data and Index Linkage
> Error Number: 7919
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.