Follow these steps when the allocation errors affect a single table and the Object ID and Index ID are known, including errors 7939 and 7949:
Look at the value for the object ID in the error message. If it is 100 or greater, the object is a user table and you should continue with “Action for User Tables”. If it is below 100, the object is a system table and requires a different procedure described in the section “Action for System Tables”.
If the object ID from the error message is 100 or greater, follow these steps to correct the error:
Check the value of the index ID in the error message to determine whether it is a table (value is 0) or an index (value is greater than 0).
Run dbcc tablealloc or dbcc indexalloc, depending on whether the object is a table or an index as determined in step 1. Before you run either command, keep these facts in mind:
dbcc tablealloc corrects this problem on a table or an index, but if the problem is on an index, you can avoid affecting the entire table by using dbcc indexalloc. If the table is large or heavily used, if may be most practical to use dbcc indexalloc.
These commands can correct the error only when run in the full or optimized mode, and with the nofix option not specified, the default for user tables.
Use the command appropriate for your situation:
For Tables (index ID = 0) |
For Indexes (0 < index ID < 255) |
|---|---|
1> dbcc tablealloc (object_ID) 2> go |
1> dbcc_indexalloc (object_id, 2> index_id) 3> go |
Refer to “dbcc” in the Adaptive Server Enterprise Reference Manual and ”Checking database Consistency” in the System Administration Guide for information about dbcc tablealloc and dbcc indexalloc.
If the object ID is less than 100, follow these steps to correct the error:
Put the affected database in single-user mode:
If the database is master, use the procedure in “How to Start Adaptive Server in Single-User Mode”, and then go to step 2.
If the database is not master, use the sp_dboption stored procedure to put the affected database in single-user mode:
1> use master 2> go
1> sp_dboption database_name, single, true 2> go
1> use database_name 2> go
1> checkpoint 2> go
Check the value of the index ID in the error message to determine whether it is a table (value is 0) or an index (value is greater than 0).
Run dbcc tablealloc or dbcc indexalloc, depending on whether the object named in the error message is a table or an index. Then execute the appropriate command, using the object ID from the error message. Before you run the appropriate command, keep these facts in mind:
dbcc tablealloc corrects either a table or an index, but if the problem is on an index, you can avoid affecting the entire table by using dbcc indexalloc. If you need to minimize the amount of time the table is unavailable, it may be most practical to use dbcc indexalloc.
These commands correct the error only when run in the full or optimized mode, with the fix option specified, because the default value is nofix on system tables.
Use the command appropriate for your situation:
For Tables (index ID = 0) |
For Indexes (0 < index ID < 255) |
|---|---|
1> dbcc tablealloc (object_ID, 2> full, fix) 3> go |
1> dbcc indexalloc (object_ID, 2> index_ID, full, fix) 3> go |
Turn off single-user mode in the database:
If the database is master, use “Returning Adaptive Server to Multiuser Mode”.
If the database is not master, use the following procedure:
1> use master 2> go
1> sp_dboption database_name, single, false 2> go
1> use database_name 2> go
1> checkpoint 2> go
Refer to “dbcc” in the Reference Manual and “Checking Database Consistency” in the System Administration Guide for information about dbcc tablealloc and dbcc indexalloc.
