Filter
Top Products
40
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.
1 Start the server with the
-iqdroplks switch in the start_asiq command (on
UNIX) or asiqsrv12 command.
2 Set dbcc_option to 3, as a temporary option:
SET TEMPORARY OPTION dbcc_option = 3
3 Run 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
5 Run the stored procedure again:
sp_iqcheckdb
6 From the report generated, drop the objects reporting errors.
7 With dbcc_option still set to 2, rerun the stored procedure to ensure no
errors are present:
sp_iqcheckdb
8 Reset dbcc_option to the default value, 0:
SET OPTION dbcc_option = 0