The following procedure uses sp_iqcheckdb, in conjunction with the -iqdroplks server startup option, to repair database allocation problems. Note that sp_iqcheckdb 'allocation database' is the only DBCC command allowed with the -iqdroplks switch.
The following procedure uses the -gd and -gm switches
to restrict database access. For a more restrictive method, see “Restricting database access during recovery”.
Repairing allocation problems using DBCC:
Start the server with the -iqdroplks option and database name in the start_asiq command line. For example:
start_asiq -n my_db_server -x 'tcpip{port=7934}'
-gd dba -gm 1 -iqdroplks my_db /work/database/my_db.db
You specify the database name twice, first as a required option to -iqdroplks with no path (to specify the database in which to recover the leaked space), and again to specify the database to start.
You must start the database with the “.db” extension,
not “.DB”.
Sybase recommends using two server startup switches to restrict access:
Use -gd DBA so that only users with DBA authority 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.
For more information about restricting connections, see “Restricting database access during recovery”.
Run the stored procedure sp_iqcheckdb in allocation mode:
sp_iqcheckdb 'allocation database'
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_asiq on any platform or the shutdown button in the console window on Windows.
Restart the server using your usual method, without the -iqdroplks option.
After allocation problems are repaired, allocation statistics appear in the DBCC output with no errors. If allocation statistics are not in the DBCC output, check to be sure that sp_iqcheckdb was run in repair mode. If errors other than multiply owned block errors are in the DBCC output, check that the -iqdroplks server startup option was specified with the correct database name in the server command line options.
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.
In this example, the server is started using the -iqdroplks option. The following commands are executed:
sp_iqcheckdb 'allocation database'; checkpoint;
The sp_iqcheckdb output indicates no errors, so the checkpoint is executed.
Note that since DBCC check mode is used in this example to repair the allocation problems, all of the indexes are also checked for consistency. 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 | ==============================|===================================|=====
When performing forced recovery or leaked blocks recovery, you must start the database with the “.db” extension, not “.DB”. For example:
start_asiq -n my_db_server -x 'tcpip{port=7934}'
-gd dba -iqdroplks my_db /work/database/my_db.db
