Dropping inconsistent indexes, tables, or columns

If sp_iqcheckdb reports unrepairable indexes, columns, or tables, then these objects must be dropped using the DROP INDEX, ALTER TABLE DROP COLUMN, or DROP TABLE statements respectively.

If you cannot drop an inconsistent object, set the temporary FORCE_DROP option. FORCE_DROP causes the IQ server to silently leak the on-disk storage of the dropped object, rather than try to reclaim it. You can recover the leaked space later using DBCC. This is desirable for an inconsistent object, because the only information about the storage of an object is within the object itself, and this information is suspect for an inconsistent object.

1. Restart the server.

   start_asiq -n bad_db_server -x 'tcpip{port=7934}' 
   -gm 1 -gd dba bad_db.db
   

   In this command, -gm 1 allows only one user to connect, and -gd restricts access to the DBA. You must not allow other users to connect when force dropping objects.

2. Set the temporary option FORCE_DROP to ON.

   set temporary option FORCE_DROP = 'ON'
   

3. Drop all inconsistent objects.

   Use the commands DROP INDEX, ALTER TABLE DROP COLUMN, or DROP TABLE as needed. Do not enter any other DDL or DML commands until after restarting the server.

4. Restart the server.

   To recover the leaked space and update the allocation map to the correct state, start the server with the -iqdroplks option set. You can set this option now, as shown in this example, or whenever you restart the server.

   start_asiq -n bad_db_server -x 'tcpip{port=7934}' 
   -iqdroplks bad_db bad_db.db
   

   You specify the database name twice, once to specify which database you are starting (with no path), and once to identify the database for the -iqdroplks option (pathname optional).

5. Run sp_iqcheckdb.

   sp_iqcheckdb 'allocation database';
   

   This step resets the database allocation map to the calculated allocation map.