Repairing Allocation Problems using DBCC

Use sp_iqcheckdb dropleaks to repair database allocation problems.

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

Start the server.
For example:
```
start_iq -n my_db_server -x 'tcpip{port=7934}' 
-gd dba -gm 1 /work/database/my_db.db
```
Note: You must start the database with the “.db” extension, not “.DB”.
Use two server startup switches to restrict access:
- Use -gd DBA so that only users with the SERVER OPERATOR system privilege can start and stop databases. (Note that the client must already have a connection to the server to start or stop the database, so this switch does not prevent connections.)
- Use -gm 1 to allow a single connection plus one DBA connection above the limit so that a DBA can connect and drop others in an emergency.
Run the stored procedure sp_iqcheckdb in dropleaks mode:
```
sp_iqcheckdb 'dropleaks database'
```
If one or more dbspaces are offline, you can repair allocation problems for a dbspace alone by running:
```
sp_iqcheckdb 'dropleaks dbspace dbspace-name'
```
If the allocation repair is successful, sp_iqcheckdb displays the message “Freelist Updated.” If errors are detected, sp_iqcheckdb returns the messages “Freelist Not Updated” and “Errors Detected.”
Stop the server after sp_iqcheckdb finishes. To stop the server, use stop_iq on UNIX or the shutdown button in the console window on Windows.

After allocation problems are repaired, allocation statistics appear in the DBCC output with no errors.

DBCC displays an Allocation Summary section at the top of the report, which lists information about allocation usage. The Allocation Statistics section provides more details about the blocks. The DBCC output does not contain repair messages for the leaked blocks that have been recovered.

For example:

sp_iqcheckdb 'dropleaks dbspace mydbspace';
checkpoint;

The sp_iqcheckdb output indicates no errors, so the checkpoint is executed.

DBCC reports statistics that do not show in this abbreviated output.

          Stat                            Value                    Flags
==============================|===================================|=====
DBCC Allocation Mode Report   |                                   | 
==============================|===================================|=====
   DBCC Status                |Freelist Updated                   |
   DBCC Status                |No Errors Detected                 | 
   DBCC Work units Dispatched |75                                 | 
   DBCC Work units Completed  |75                                 | 
==============================|===================================|=====
Allocation Summary            |                                   | 
==============================|===================================|=====
   Blocks Total               |8192                               | 
   Blocks in Current Version  |4594                               | 
   Blocks in All Versions     |4594                               | 
   Blocks in Use              |4610                               | 
   % Blocks in Use            |56                                 | 
==============================|===================================|=====
Allocation Statistics         |                                   | 
==============================|===================================|=====
   DB Extent Count            |1                                  | 
   Marked Logical Blocks      |8176                               | 
   Marked Physical Blocks     |4594                               | 
   Marked Pages               |511                                | 
   Blocks in Freelist         |126177                             | 
   Imaginary Blocks           |121567                             | 
   Highest PBN in Use         |5425                               | 
   Total Free Blocks          |3582                               | 
   Usable Free Blocks         |3507                               | 
   % Free Space Fragmented    |2                                  | 
   Max Blocks Per Page        |16                                 | 
   1  Block Page Count        |103                                | 
   3  Block Page Count        |153                                | 
   ...
   16 Block Hole Count        |213                                | 
==============================|===================================|=====

Note: When performing forced recovery or leaked blocks recovery, you must start the database with the “.db” extension, not “.DB”. For example:

 start_iq -n my_db_server -x 'tcpip{port=7934}'
-gd dba -iqfreq my_db /work/database/my_db.db