Sybase Comprehensive Guide on Using sp_iqcheckdb for Database Recovery

The dbcc_option settings of 0 and 3, when combined with the server option -iqdroplks, update the free list if no errors are detected. In order to perform this function, write transactions are prevented before and during the running of sp_iqcheckdb. The stored procedure ensures this by taking the appropriate locks during its execution. Any write transactions are blocked while sp_iqcheckdb is running.

If it detects transactions that are not committed or not checkpointed,

sp_iqcheckdb may refuse to recover leaked blocks. If this occurs, issue a checkpoint command and rerun sp_iqcheckdb. If sp_iqcheckdb still refuses to run, other users with active write transactions are connected to the database.

❖To recover leaked space:

In the event that the default option (dbcc_option = 0) cannot recover the free list, and a previous backup is not available, use the following procedure to try to recover the database.

1Start the server with the -iqdroplksswitch in the start_asiq command (on UNIX) or asiqsrv12 command.

2Set dbcc_option to 3, as a temporary option:

SET TEMPORARY OPTION dbcc_option = 3

3Run the stored procedure: sp_iqcheckdb

Note If this procedure fails, it is likely that the database is corrupt and beyond repair.

4 Set dbcc_option to 2, as a temporary option:

SET TEMPORARY OPTION dbcc_option = 2

5Run the stored procedure again: sp_iqcheckdb

6From the report generated, drop the objects reporting errors.

7With dbcc_option still set to 2, rerun the stored procedure to ensure no errors are present:

sp_iqcheckdb

8Reset dbcc_option to the default value, 0:

SET OPTION dbcc_option = 0

40