Recovering Leaked Space

Use the sp_iqcheckdb stored procedure in dropleaks mode to recover leaked storage space within the specified database.

An allocation map is used by the server to determine if a page is in use or not in use within IQ. Either through system failure or as a result of opening a database with forced recovery, the allocation map of the database may not reflect the true allocation of its usage. When this occurs, we say that the database has “leaked” storage or “leaked blocks.” In general, you need not be concerned about small numbers of leaked blocks. If you have many megabytes of leaked blocks, you probably want to recover that space.

When leaked storage is being recovered, other transactions that alter the allocation map are shut out. Such operations include checkpoints and commands that modify the database.

You can recover leaked storage and force recovery either at the same time or separately. To recover leaked space within a database without doing a forced recovery, repair allocation problems using DBCC. To recover leaked space within a database after doing a forced recovery, recover leaked space using this procedure.

If repairing allocation problems using DBCC fails to recover leaked storage, then use this procedure.

Note: This procedure uses the -gd and -gm switches to restrict database access. For a more restrictive method, start the server in forced recovery mode.

Start the server with the -iqfrec option in the start_iq command.
```
start_iq -n my_db_server -x 'tcpip{port=7934}' 
-gd dba -gm 1 
-iqfrec my_db /work/database/my_db.db
```
You specify the database name twice in a row, once to specify it as the database you are starting, and once to specify it as the database undergoing forced recovery. The -iqfrec option requires the database name.
Connect to the database you are recovering.
Run the stored procedure sp_iqcheckdb in dropleaks mode.
```
sp_iqcheckdb 'dropleaks database'
```
If there are no errors and sp_iqcheckdb displays the message Freelist Updated, you have recovered leaked space and forced recovery. Continue to the next step.

If inconsistency is found, drop inconsistent indexes, tables, or columns. Then run sp_iqcheckdb again to recover leaked space.
Issue a checkpoint.
Stop the server using your usual method.
Restart the server using your usual method, and proceed with normal processing.