sp_dbcc_patch_finishtime

Description

Facilitates reporting on aborted checkverify and checkstorage operations.

Syntax

sp_dbcc_patch_finishtime dbname, opid [,optype [,seq [,finishtime]]]

Parameters

dbname

is the name of the database checkstorage or checkverify was operating on when it aborted. This parameter’s type is varchar.

opid

is the operation ID corresponding to the aborted operation. This parameter’s type is smallint.

optype

is the type of operation you are investigating. Accepted values are either ‘checkstorage’ or ‘checkverify’. This parameter’s type is varchar.

seq

is the checkverify sequence number (not used for checkstorage but required for checkverify). This parameter’s type is smallint.

finishtime

A datetime value representing the time the checkstorage or checkverify operation aborted. The default value is the current time.

Examples

Example 1

Enables reporting on checkstorage and checkverify for database my_db when the following errors occur:

dbcc checkstorage (my_db)

Checking my_db: Logical pagesize is 2048 bytes
00:00000:00014:2003/01/20 11:50:05.01 server  Error: 9960, Severity: 20, State: 1
A non-recoverable error has occurred in the CHECKSTORAGE operation. The 
operation has been aborted.

Msg 9970, Level 20, State 1:
Line 2:
DBCC cannot update the finish time in dbcc_operation_log table for this
operation(opid = '1') of database 'my_db'. This can be patched by executing
sp_dbcc_patch_finishtime.

Example 2

Enables reporting on checkstorage and checkverify for database my_db when the following errors occur:

dbcc checkstorage (my_db)

Checking my_db: Logical pagesize is 2048 bytes
00:00000:00014:2003/01/20 11:50:05.01 server  Error: 9960, Severity: 20, State: 1
A non-recoverable error has occurred in the CHECKSTORAGE operation. The 
operation has been aborted.

Msg 9970, Level 20, State 1:
Line 2:
DBCC cannot update the finish time in dbcc_operation_log table for this
operation(opid = '1') of database 'my_db'. This can be patched by executing
sp_dbcc_patch_finishtime.

Execute sp_dbcc_patch_finishtime with the information included in the error message:

sp_dbcc_patch_finishtime my_db, 1

Usage

When a checkstorage or checkverify operation aborts, it prints a message that contains the operation’s ID and the name of the database that was being examined when the operation aborted. An aborted checkverify operation also provides a sequence number in the message. The message instructs the user to run sp_dbcc_patch_finishtime, and provides the dbname, opid, and if it was a checkverify operation, the sequence number, seq. After executing sp_dbcc_patch_finishtime, you can create fault reports on the aborted operation.

Permissions

Only a System Administrator or the Database Owner can run sp_dbcc_patch_finishtime.