DBCC CheckDB timeout

Hi I'm trying to figure out a way of getting the DBCC CheckDB command to timeout in the event a table in my database is locked.

Lets say I run this stored procedure:

Begin tran
Select * from NumbersTable with(tablockx)

Of course, the NumbersTable in my database is locked.
Now I run DBCC CheckDB('database'), which will hang because the stored procedure above caused a block/lock on the table.

My question is, given this situation is there anyway to set some sort of timeout for DBCC CheckDB so it doesn't run forever?

And yeap, I know I can commit the transaction or kill the lock so DBCC CheckDB can resume. But I'm looking for a way of getting it to timeout.

I've tried the command SET LOCK_TIMEOUT 10000 but that doesn't seem to do anything.

Thank you all.

I don't quite understand why you'd want to do this, but...

one way that might work - you could call the DBCC command by invoking OSQL with the -t <timeout> switch.
(haven't tried it, but it seems to do the trick)

/Kenneth

|||Hi thank you for the reply.
The reason I want to do this is because I've written a C++ utility that goes through all databases in SQL Server and runs a maintanence script on the databases.

Here is the script:

USE master
DBCC CHECKDB (‘<database name>’)
DBCC SHRINKDATABASE (‘<database name>’)
USE <database name>
EXEC sp_MsForEachTable 'DBCC DBREINDEX (''?'', '''', 0)'
EXEC sp_createstats
EXEC sp_updatestats
USE master
DBCC UPDATEUSAGE ('<database name>')

Then when a corruption is found, I run CheckDB in repair mode:
EXEC sp_dboption '<database name>',single,true
DBCC CHECKDB (‘<database name>’, <advised repair command>)
EXEC sp_dboption '<database name>',single,false

When a table in a database is locked, then the DBCC CheckDB part will hang. Sure the chances of it actually happening are small (only been manually locking it), but if possible I'd like to know a way of setting a timeout.
I've tried SET LOCK_TIMEOUT 10000 before CheckDB but it doesn't work.
Is there anyway to include your OSQL timeout into the script above?

Any help is appreciated.
|||

As I understand it (haven't actually used this parameter), the timeout switch in OSQL would terminate the command that OSQL is running when the value is exceeded.

The script, being a 'normal' sql script, can be called from OSQL by the /i <myscriptfile.sql> switch.

Though, I'm having doubts about running DBCC in repairmode in such an automated way.
You are fully aware of the possibilites that you may experience dataloss with this option?
Is this really the intention? Normally, each case of suspected corruption or DBCC error needs some 'human evaluation' in order to decide what action to perform next.

/Kenneth