This section contains error messages for the Adaptive Server Page Manager.
17
Can't allocate space for object '%.*s' in database '%.*s' because '%.*s' segment is full/has no free extents. If you ran out of space in syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase size of the segment.
This error occurs when Adaptive Server is unable to allocate space in a database. The state of the error indicates what type of space could not be allocated:
Type of Space That Cannot Be Allocated |
|||
|---|---|---|---|
State 1 |
State 2 |
State 3 |
State 4 |
Data space |
Extent allocation space |
Log space |
Log space |
1105 errors are written to the error log only when they occur during the checkpoint process. When a user process encounters this error, the message is returned to the client application without being written to Adaptive Server's error log.
To correct the error, you must know:
The Server version level.
Whether the error occurred during run time or during recovery:
Run time is a period during which Adaptive Server performs normal daily activities, such as receiving a query from a client application, processing a transaction, or returning results from a transaction.
An Adaptive Server is in recovery when doing activities such as restarting, performing the processing that occurs after a database dump is loaded, and performing the processing that occurs after a transaction dump is loaded.
The state of the error: 1, 2, 3, or 4.
Use the sections below to correct the error, depending on whether the error was at run time or during recovery.
If the error occurred during runtime, use the next section to correct the error. Refer to “Recovery 1105 Errors” if the error occurred during recovery.
The data segment is full on the indicated data base. To obtain more data space, do one or both of the following:
Use alter database or sp_extendsegment to increase the size of the data segment.
Drop objects from the database or delete rows from a table.
The log segment is full on the indicated database. To clear space in the log, perform the following steps:
Determine approximately how many pages the transaction log occupies using the commands:
1> use database_name 2> go
1> select data_pgs (8, doampg) 2> from sysindexes where id=8 3> go
where “8” is the ID for syslogs. The result is the number of data pages (2K pages on most operating systems) that the transaction log occupies.
The query results may be inaccurate by as many as 16
pages, but using the query is much faster than counting the rows
in the transaction log.
Dump the inactive portion of the transaction log using the dump transaction command. If this command fails with the 1105 error, retry the command using the with truncate_only option.
If this command also fails with the 1105 error, run dump transaction with no_log.
Using the dump transaction with no_log command
can result in an 813 error. Therefore, do not use dump
transaction with no_log unless it is absolutely
necessary (when dump transaction with truncate_only will
not truncate the log).
Repeat step 1. If the transaction log occupies significantly fewer pages, continue with step 4..
If, however, there are still a large number of pages in the syslogs table, an outstanding transaction is probably preventing the log from being cleared. If this is the case, choose one of the following options:
Refer to “How to Detect and Clear Long-Running Transactions” in the Encyclopedia of Tasks chapter.
Restart Adaptive Server and repeat step 2. When Adaptive Server starts and this database is recovered, the outstanding transaction is rolled back, allowing the log to be cleared by a subsequent dump transaction. Depending on the size of the outstanding transaction, rolling back the transaction during startup recovery may be significant.
If the dump transaction command was executed with either the no_log or the truncate_only options in step 2, dump the database now, because these options prevent subsequent changes recorded in the log from being used to recover from a media failure. You must do a dump database now to ensure the recoverability of subsequent database modifications using transaction logs.
This database dump is not required if your backup and
recovery procedures do not include saving the transaction logs for
media failure recovery.
Do not assume that the occurrence of 1105 errors automatically means your transaction log is too small. If the data and the log are on the same segment, either the State 1 and State 2, or the State 3 actions may free enough space without increasing the size of the transaction log.
If you are concerned that your transaction log is not large enough for your Adaptive Server, refer to “Creating and Managing User Databases” in the System Administration Guide and read the section “Estimating the Transaction Log Size” before deciding to increase the log size. If appropriate, refer to the ASE Reference Manual for more information about the alter database command used to increase log size. Remember that once they have been increased, log and database sizes cannot be decreased.
The above procedures may not be effective in resolving the 1105 errors due to excessive data growth in mixed data and log segments; you may need to move the data to a different device in this situation.
The log space is full on the indicated database. LCT (last chance threshold) was trying to empty the log but ran out of space trying to log information. Perform the same procedure as in “Runtime 1105 Errors: State 3”.
If dbcc checktable on syslogs shows space available in the transaction log, check your sysusages system table. Error 1105 may occur if the unreservedpgs column for this database has NULL values in it when it should have numeric values. Update that column to 0 (instead of NULL).
You may see 1105 errors when the log is full (States 3, 4) on a database that is replicated or has been loaded from a replicated database. This occurs because Adaptive Server is unable to truncate the log past the LTM truncation point, even with truncate log on checkpoint set.
To correct this problem, instruct the server to ignore the LTM truncation point for the database:
1> dbcc settrunc (ltm, ignore) 2> go 1> dump transaction with truncate_only
2> go
The server will now be able to truncate the log.
1105 errors occur on tempdb while Adaptive Server is processing transactions, never during recovery.
The tempdb transaction log behaves just like a user database transaction log with the sp_configure option trunc log on chkpt set on. But data management is more difficult with tempdb because it is more difficult to get information about space use: worktables cannot be referenced by the user, and the log is truncated approximately every 60 seconds. The default size of tempdb is 2MB when Adaptive Server is installed.
Read all relevant sections about transaction log management
in the System Administration Guide and the Reference
Manual before making any changes at your site.
Possible causes of the 1105 error in tempdb, and their symptoms and solutions include:
Symptom |
Possible Causes |
Solution |
|---|---|---|
Too many transactions fill up tempdb transaction log. |
Doing too much work in tempdb (or temporary tables) fills up the log. |
Design your application for more efficient use of tempdb. |
Processing more transactions per 60 seconds than can be logged in tempdb with current space. |
Increase size of tempdb's transaction log (refer to alter database in theReference Manual for details) or perform manual dump transaction with truncate only commands on tempdb. |
|
One transaction is too big. |
Your transaction may create a Cartesian product which fills the log. There may be too many steps between the begin transaction and the commit transaction. A single statement such as insert, delete or update may involve so many rows that the transaction log fills up before the statement can finish. |
Rewrite the transaction to avoid Cartesian products and long individual queries. Read about transaction log management in the System Administration Guide and the Reference Manual for information and suggested strategies. |
If the 1105 error occurred during run time, use the section “Runtime 1105 Errors” to correct the error. If the error occurred during recovery, use the procedure below which corresponds to the database on which the 1105 error occurred:
A user database
The master database
The model database
Use the following procedure to correct 1105 errors on a user database which occur during recovery:
Manually set the database's status to no chkpt on recovery and single-user:
WARNING! Because the database was marked suspect on the original recovery attempt, this procedure also resets some internal status bits to allow the database to recover normally. Therefore, do not use this procedure under any other circumstances, or you may create additional serious problems.
1> sp_configure "allow updates", 1 2> go
1> begin transaction 2> go
1> update master..sysdatabases 2> set status = status & ~256 3> where name = "database_name" 4> go
1> update master..sysdatabases 2> set status = status | 4112 3> where name = "database_name" 4> go
Check that each of the above update commands affected only one row. If more than one row was affected, issue a rollback transaction. Otherwise, commit the transaction and shut down Adaptive Server:
1> commit transaction 2> go
1> shutdown 2> go
Restart Adaptive Server.
Dump the transaction log with the no_log option and reset the database status:
1> use master 2> go
1> dump tran database_name with no_log 2> go
1> sp_dboption database_name, "no chkpt", false 2> go
1> sp_dboption database_name, single, false 2> go
1> use database_name 2> go
1> checkpoint 2> go
1> sp_configure "allow updates", 0 2> go
If an 1105 error occurs on the master database during recovery and Adaptive Server will not run, correct the problem with the following procedure:
Create a runserver file that includes the 3607 trace flag. This trace flag allows Adaptive Server to restart without going through normal recovery, and should only be used in this procedure. Refer to “How to Start Adaptive Server with Trace Flags” in the Encyclopedia of Tasks chapter for instructions.
Start Adaptive Server with the 3607 trace flag using the runserver file created in step 1. The recovery process will perform the minimum actions required to allow access to each database, including master.
Once Adaptive Server has restarted and the master database is accessible, dump the transaction log with the no_log option and shut down the Adaptive Server:
1> dump tran master with no_log 2> go
1> shutdown with nowait 2> go
It is mandatory that you use shutdown with
nowait here. Failure to use the with nowait option
will suicide every database's transaction log.
Restart Adaptive Server with the original runserver file. The database should recover normally. Now dump the master database:
1> dump database master 2> to logical_dump_device 3> go
If an 1105 error occurs on the model database during recovery, Adaptive Server may not start. The error occurs because the tempdb database is required to start Adaptive Server, but it cannot be built due to the problem with model. To correct the problem and restart Adaptive Server follow these steps:
Create a new runserver file that includes the 3608 trace flag. This trace flag causes Adaptive Server to recover only the master database. Refer to “How to Start Adaptive Server with Trace Flags” in the Encyclopedia of Tasks chapter for instructions.
Start Adaptive Server with the 3608 trace flag using the runserver file created in step 1.
Manually set model status to no chkpt on recovery and single-user, as follows:
1> sp_configure "allow updates", 1 2> go
1> begin transaction 2> go
1> update master..sysdatabases 2> set status = status & ~256 3> where name = "model" 4> go
1> update master..sysdatabases 2> set status = status | 4112 3> where name = "model" 4> go
Check that each of the above update commands affected only one row. If more than one row was affected, issue a rollback transaction. Otherwise, commit the transaction and shut down Adaptive Server:
1> commit transaction 2> go
1> shutdown with nowait 2> go
Restart Adaptive Server, using your regular runserver file.
Execute the following command to truncate the inactive portion of the transaction log in model:
1> dump tran model with no_log 2> go
Reset the database options on model with the following command:
1> sp_dboption model, "no chkpt", false 2> go
1> sp_dboption model, single, false 2> go
1> use model 2> go
1> checkpoint 2> go
Disable updates to the system catalog and shut down Adaptive Server:
1> sp_configure "allow updates", 0 2> go
1> shutdown with nowait 2> go
It is mandatory that you use shutdown with
nowait here. Failure to use the with nowait option
will suicide every database's transaction log.
Restart Adaptive Server.
All versions
21
Cannot deallocate extent %ld, database %d. Object id %ld, index id %d, status %d in extent does not match object id %ld, index id %d, status %d in object being deallocated. Run DBCC CHECKALLOC.
Error 1108 occurs when Adaptive Server is deallocating an extent, if the extent information on the allocation page does not match the information for the object being deallocated. This can be caused by corruption of the allocation page or memory corruption.
A software problem can cause this error to occur:
During rollback of a transaction
When rows for an object are being deleted or updated
When an object is being dropped
This is a serious error as it indicates corruption has occurred.
Error 1108 is only raised by diagserver.
Run dbcc checkalloc on the database named in the message. Resolve any errors reported by dbcc checkalloc using information about those errors in this manual.
If errors still exist, restore the database from a clean backup or call Sybase Technical Support.
If you need assistance from Sybase Technical Support, have the following information available when you call:
Server version and SWR version level
Server error log
Text of all error messages
Operating system error log
All versions
20
Attempt to call pg_readalloc on page %ld which is not an allocation page.
Allocation pages are used to track space in the database. Each allocation page for an object has an entry in the Object Allocation Map (OAM) for that table or index. The OAM entry for each allocation page stores the number of used and unused data/index pages on that allocation page.
Error 1120 occurs when you have an invalid page number for an allocation page in the allocation section of an OAM. In this case, Adaptive Server will not have accurate information about which pages have been allocated.
This is a very serious error as Adaptive Server cannot correctly insert or update data when it occurs. Action should be taken immediately or further database corruption will occur.
Use the page number in the error message to identify the object (and thus the database) that is affected by the error. Refer to“How to Find an Object Name from a Page Number” in the Encyclopedia of Tasks chapter for details.
To recover from Error 1120, restore your database either using bcp or from clean backups. Refer to “How to Rescue Data from a Corrupted Table” in the Encyclopedia of Tasks chapter for information about using bcp to save your data.
For information about OAM pages, refer to ”Checking Database Consistency” in the System Administration Guide and see the section “Understanding Page and Object Allocation Concepts”.
If you need assistance from Sybase Technical Support, have the following information available when you call:
Server version and SWR version level
Server error log
Text of all error messages
Operating system error log
Output of dbcc page for the corrupted page
Output of dbcc checkalloc for the database identified by dbcc page
All versions
20
Failed to get lock on allocation page %ld.
Adaptive Server uses allocation pages to track space in the database. Allocation pages are updated when an object’s space requirements change.
Adaptive Server raises Error 1124 due to the following sequence of events:
A server process acquires a lock on an object’s allocation page in order to allocate pages for the object.
An exception occurs during allocation. This necessitates "undoing" the allocation steps.
The server is unable to acquire a lock necessary to abort the allocation, resulting in Error 1124.
This error may be accompanied by a kernel error ("Current process infected with %d"); Error 945 ("Unable to locate page %ld for database id %d in the in-memory copy of Sysusages (spid=%ld). This is an internal error. Please contact Sybase Technical Support."); Error 1205 ("Your server command (family id #%d, process id #%d) was deadlocked with another process and has been chosen as deadlock victim. Re-run your command.") or other errors.
Error 1124 is due to an Adaptive Server problem when rolling back space allocation.
When Error 1124 is accompanied by a 945 error on tempdb (dbid = 2), which is not resolved by shutting down and restarting the server, there may be insufficient space on tempdb or a problem with tempdb storage. You can resolve these problems by following the instructions in “How to Reduce the Size of tempdb” in the Encyclopedia of Tasks chapter.
In all other instances of the 1124 error, call Sybase Technical Support.
Have the following information ready before calling Sybase Technical Support:
Server version and SWR rollup level
Server error log
11.5.x and earlier
22
Internal Error: The OAM has no room for new inserts. Check OAM for object %ld, index %d.
The Object Allocation Map ( OAM) tracks information about the allocation and deallocation of storage space for tables and indexes. At least one OAM page exists for each table (except syslogs and sysgams) and for the table's indexes. The OAM page has an entry for each allocation page used by the object. The first OAM page for an object has a maximum of 240 entries, and any subsequent OAM pages have a maximum of 250 entries per page.
When a new page is allocated for the table or index:
The appropriate OAM page is updated.
If necessary, a new OAM entry is created for the allocation page which controls the page being added.
If necessary, a new OAM page is created and linked in to the current OAM page chain for the object. This occurs when there are no available slots for a new OAM entry in any existing OAM page for the object.
The total number of entries in the object's OAM (corresponding to the total number of allocation pages with extents for this object) is adjusted during table updates and also during database recovery.
Error 1127 is raised:
If the total number of entries for all OAM pages for the object exceeds its upper limit (overflow in OAM page header). This can happen if the table grows and occupies space on many allocation pages.
If the total number of entries is corrupted. This is due to an Adaptive Server problem.
There are different ways to correct this error, depending on the conditions under which it occurred and the nature of the OAM problem. Other errors may also be raised at the same time.
If this error is raised when you attempt to start Adaptive Server and the Server hangs, the database may be marked suspect; contact Sybase Technical Support for assistance.
If the error occurs during a table update (for example during a bulk copy operation), follow these steps:
If the object is a system table (a system table's object ID is less than 100) and the index ID is not 0, refer to “How to Fix a Corrupted Index on System Tables”in the Encyclopedia of Tasks chapter for instructions on how to repair the system table index.
If the object is a system table and the index ID is 0, contact Sybase Technical Support. They may be able to help you repair the corruption but it may be necessary to restore the database from clean backups.
If the object encountering the error is not a system table, continue with step 2.
If this is a user table, the problem may be due to an overflow in the OAM page header (although this is unlikely), or corruption of the page header. Run the following command to display OAM page information (you must have sybase_ts_role granted to you to run this and also run dbcc traceon(3604) first):
1> dbcc listoam(database_name, object_ID, index_ID) 2> go
For example:
1> dbcc listoam(pubs2, 104001776, 0) 2> go
--------------------------------------------------- Objid: 104001776 indid: 0 OAM pg cnt: 1 Entry cnt: 1 Rows: 18 Rows Per pg: 6 Used pgs: 4 Unused pgs: 4 OAM status bits set: PG_OAMPG, PG_OAMSORT OAM pg # 520 has the following entries (allocpg:used/unused): 512: 4/ 4 ---------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
Call Sybase Technical Support with this information.
For more information about OAM pages, refer to the section ”Understanding Page and Object Allocation Concepts” in “Checking Database Consistency” in the System Administration Guide.
Before calling Sybase Technical Support, have the following information ready:
Server version and SWR version level
Text of all error messages
Server error log
Output of dbcc listoam command for the table
All versions
10
The requested update to the OAM for object %ld, dbid %ld, index %d, oam page %ld, would cause a negative page count. allocation page: %ld, oamarray used: %ld, oamarray unused: %ld, deltaused: %ld, deltaunused: %ld, spid: %ld. Contact your System Admin.
Most tables and all indexes have at least one Object Allocation Map (OAM) page. A single OAM page holds information about up to 250 allocation pages (248 if the object is an identity column). Each allocation page for an object has an entry in the OAM pages for that table or index. The OAM entry for each allocation page stores the number of used and unused data and index pages on that allocation page.
When a new page is needed or a page is deallocated, the OAM entry has to be updated. The number of used and unused pages on the allocation page is not allowed to be less than 0. Error 1129 occurs if the number becomes less than 0.
Look at the error message to determine the object ID and the index ID. If the object encountering the error is not a system table (a system table's object ID is less than 100), continue with step 2.
If the object is a system table and the index ID is not 0, refer to “How to Fix a Corrupted Index on System Tables” for instructions on how to repair the system table index.
If the index ID is 0, contact Sybase Technical Support. They may be able to help you repair the corruption but it may be necessary to restore the database from clean backups.
For user tables, if the index ID is 0 or 255, continue with step 3.
If the index ID is not 0 or 255, translate it into an index name:
1> use database_name 2> go
1> select name from sysindexes 2> where id = object_ID and indid = index_ID 3> go
To ensure that the information needed to re-create the index is available, run sp_helpindex on the index prior to dropping it.
Drop the index.
Re-create the index. This clears the corruption in most cases.
Run dbcc checktable on the table to verify that the corruption is gone.
If the index ID is 0 or 255, do one of the following:
Restore the database from clean backups.
Refer to “How to Rescue Data from a Corrupted Table” in the Encyclopedia of Tasks chapter.
For more information about OAM pages, refer to the section “Understanding Page and Object Allocation Concepts” within “Checking Database Consistency” in the System Administration Guide.
If you need to call Sybase Technical Support, have the following information ready:
Server version and SWR version level
Text of all error messages
Server error log
dbcc listoam(database_name, object_ID, index_ID) output (you must have sybase_ts_role granted to you to run this and also run dbcc traceon (3604) first)
All versions
22
The OAM for object %ld, index %d, oam page %ld, database=%d, is currently allocated to another object.
The Object Allocation Map (OAM) tracks information about the allocation and deallocation of storage space for tables and indexes. At least one OAM page exists for each table (except syslogs and sysgams) and for the table's indexes. Each allocation page used by the object has an entry in the OAM page, showing the number of used and unused data/index pages on that allocation page.
Error 1131 is raised when Adaptive Server attempts to validate an OAM page belonging to an object, and determines that the page is actually allocated to a different object.
Error 1131 occurs with the following states:
State |
Meaning |
1 |
While updating the used and unused counts for an OAM entry, the OAM page is found to be allocated to a different object. This error is raised only by diagserver. |
3 |
While checking the validity of an OAM page, it is found that the page is allocated to a different object. This state of Error 1131 is raised in versions 11.5.x and earlier. |
1131 errors are often seen when you use the system functions data_pgs, reserved_pgs, used_pgs, or rowcnt in a very active database, or a highly volatile database such as tempdb, where objects are being created and dropped frequently. The error can occur:
if you use and invalid object id in the function;
if the page is allocated to another object, or is uninitialized.
Use trace flag 1116 to suppress the 1131 error when using these system functions in an active or volatile database environment.
For help with trace flags, see “How to Start Adaptive Server with Trace Flags” in the Encyclopedia of Tasks chapter.
All versions
22
Page %ld was expected to be an OAM page for %ld and it is not.
Every Object Allocation Map (OAM) page has a unique number in the database. Error 1133 occurs when an attempt by Adaptive Server to retrieve an OAM page by specifying the page number fails because there is no OAM page in the current database having that page number.
Error 1133 can occur when checkalloc is run if what is expected to be the OAM page in the OAM page linkage is not an OAM page. The linkage is accessed via the doampg or ioampg column for the object in sysindexes. (doampg is the sysindexes pointer to the data pages' OAM. ioampg is the sysindexes pointer to the index pages' OAM.)
This error can also occur when, due to a problem with Adaptive Server, the OAM page number that is requested is greater than the maximum page number for the database.
In these cases, Error 1133 is a serious error and may result in reading the wrong OAM page into data cache and corrupting a buffer in the process.
Use the procedure described in “How to Find an Object Name from a Page Number”in the Encyclopedia of Tasks chapter to translate the page number displayed in the error message into an object ID and an index ID.
If the corruption is on a user table's index (object ID is 100 or greater, and the index ID is between 1 and 250), dropping and re-creating the index should clear the corruption.
Use sp_helpindex to list all indexes on the table and then rebuild all the nonclustered indexes using one of two methods:
If the index ID is between 1 and 250, drop and re-create each nonclustered index on the table.
Or:
If a clustered index also exists on the table, drop and re-create it, which causes all nonclustered indexes to be automatically rebuilt. If your table is large, you may not have the space to do this (a rule of thumb is that 150 percent of your table size must be available).
If the corruption is not on an index page, run dbcc tablealloc or dbcc checkalloc on the corrupted object. The output should report OAM allocation errors. Try to fix the reported allocation errors first by referring to the appropriate writeups in this manual.
If the 1133 errors persist, call Sybase Technical Support for assistance.
Before calling Technical Support, have the following information available:
Server version and SWR version level
Server error log
Text of all the error messages
Operating system error log
Output from dbcc page for the corrupted page
Output of dbcc checkalloc for the database identified by dbcc page
All versions
26
Unexpected value returned to page manager routine: %ld.
Adaptive Server uses Object Allocation Map (OAM) pages to track space allocation for Adaptive Server objects. Error 1141 occurs when Adaptive Server tries to update an OAM page following page allocation or deallocation, but the update fails.
Error 1141 may be raised:
when the database transaction log is full, so that the necessary log records cannot be written to record page allocations.
due to an Adaptive Server problem.
The error is raised in the following states, which indicate the specific page manager activity where the OAM update failed. Pages in a given state may involve Allpages Locked (APL) tables, Data-only Locked (DOL) tables, temporary work tables or sort tables, or combinations thereof.
State |
Meaning |
|---|---|
1 |
There was an unexpected error when determining whether to update the page counts. |
2 |
OAM update failed when allocating extents; also when deallocating one or more pages in an extent. |
3 |
Update failed when doing post-commit cleanup following deallocation. |
4 |
Update failed when allocating an extent for an APL table. |
6 |
Update failed when deallocating an extent for an APL table. |
7 |
Update failed when deallocating an extent for an APL table, and there were no unused pages in extent. |
8 |
Update failed when performing OAM reallocation. Applies to both APL and DOL tables. |
9 |
Update failed when deallocating an extent for sort pages. |
10 |
Update failed when committing large-scale page deallocations (for example extents with no pages allocated). |
When Error 1141 occurs, a stack trace is written to the error log and the current process is terminated.
Other errors may sometimes accompany the 1141 error. For example, an 1105 error can also be raised when the tempdb transaction log becomes full. If other errors are reported, correct them using the information about those errors elsewhere in this manual. Then retry the transaction.
To check if the 1141 error resulted from a full transaction log, use the procedures outlined in "Estimating the Transaction Log Size" in the System Administration Guide.
If the 1141 error persists after you have checked for other errors and corrected any log space problems, call Sybase Technical Support.
Have the following information ready before calling Technical Support:
Adaptive Server version and SWR Rollup level
Server error log
All versions
22
Invalid OAM Page %ld. Found pstat=0x%x, object=%ld, database=%d, indid=%d.
Invalid OAM Page %ld. Found pstat=%d, object=%S_OBJID,database=%S_DBID,indid=%d.
Depending on what caused it, Error 1142 can be either a serious error due to actual corruption in the database or a result of misusing a system function (data_pgs, reserved_pgs, rowcnt, or used_pgs). Error 1142 can occur during run time as well as during recovery (during Adaptive Server startup, load database, or load transaction).
The error can occur from incorrect use of any of the following system functions that retrieve information about allocation pages:
data_pgs
reserved_pgs
rowcnt
used_pgs
For example, if you incorrectly type:
1> select reserved_pgs (id, root) from
2> master..sysindexes
3> where id = object_ID("table_name")
4> go
you will get Error 1142, because the reserved_pgs system function attempted to retrieve an invalid OAM page. (Replacing root in the above example with a correct OAM page, for example doampg, would give expected results.)
If you get Error 1142 and you are sure that you did not use system functions across databases, this error is a sign of serious corruption in your database. You can verify this by running dbcc tablealloc on the object or dbcc checkalloc on the database as discussed under Action, “Incorrect OAM Page Entry in sysindexes”, Step 3.
Adaptive Server uses OAM pages to track how space is allocated for Adaptive Server objects. Error 1142 occurs when Adaptive Server tries to retrieve an OAM page and the status of that page indicates that it is not an OAM page. It occurs with the following states:
State |
Meaning |
|---|---|
1 |
The status of the first OAM page indicated that it was not a valid OAM page. |
2 |
Adaptive Server encountered an invalid OAM page during recovery or during an undo or a redo of a truncate table command. |
3 |
Adaptive Server encountered an invalid OAM page while preparing to read and install an OAM page in the buffer cache. |
4 |
Adaptive Server encountered an invalid OAM page during deallocation of an object page. |
Recovery from this error depends on why the error occurred. Follow the instructions below, depending on what caused the error.
When you want to use any of the system functions specified in Explanation, “Incorrect System Function Use” across databases, select the database to be accessed with a use database command and then run the query for the current database.
If Error 1142 occurred during the threshold accounting phase of recovery, the database is marked “suspect” and the object referenced in the error message output is corrupted. To determine whether the error occurred during this phase of recovery, look in the error log to determine whether the error occurred after either of the following types of messages:
Messages like the following appear when there is nothing to recover:
00:95/07/11 15:42:56.91 server Recovery dbid 5 ckpt (4612,2) 00:95/07/11 15:42:56.92 server Recovery no active transactions before ckpt.
Messages like the following appear after transaction recovery completes:
00:95/07/11 15:42:58.11 server Recovery dbid 7 ckpt (322,26) oldest tran=(322,25) 00:95/07/11 15:42:59.14 server 469 transactions rolled forward/back.
If Error 1142 occurred after messages like these, perform the following steps to recover from the 1142 error. Otherwise, go to “Incorrect OAM Page Entry in sysindexes”.
Turn off threshold accounting for the database affected by the 1142 error so that it can complete recovery:
1> use master 2> go
1> sp_dboption database_name, 2> "no free space acctg", true 3> go
where database_name is the name of the database in the error message.
For the affected database, reset the suspect status using one of the methods supplied in Chapter 2, “How to Reset a Database's “suspect” Status”. Shut down and restart the server to complete recovery.
The object named in the error message is still corrupted. Follow the instructions in “How to Rescue Data from a Corrupted Table” in the Encyclopedia of Tasks chapter to recover the data.
Be sure to perform step 3 before going to step 4. Otherwise,
the next time you restart Adaptive Server, the database that got
the 1142 error will get it again.
Turn threshold accounting back on for the database affected by the 1142 error:
1> use master 2> go
1> sp_dboption database_name, 2> "no free space acctg", false 3> go
1> use database_name 2> go
1> checkpoint 2> go
where database_name is the name of the database in the error message.
In this case, Error 1142 is a serious error and may result in reading the wrong OAM page into data cache and corrupting a buffer in the process.
Follow these steps to clear Error 1142:
Use the procedure described in “How to Find an Object Name from a Page Number” in the Encyclopedia of Tasks chapter to get dbcc page output for the page specified in the 1142 output. If the page status bits row has the value 0x8000, the page is a valid OAM page. If the value 0x8000 does not appear in the dbcc page output for the page status bits row, the page is not a valid OAM page and the object is corrupted. If it appears that the object is not corrupted, call Sybase Technical Support to determine why the 1142 error occurred.
If the corruption is on a user table's index (object ID is 100 or greater and index ID is between 1 and 250), run dbcc checktable to verify that the data page linkage is good. If checktable runs cleanly, dropping and re-creating the index should clear the corruption.
Use sp_helpindex to list all indexes on the table, and then rebuild all the affected nonclustered indexes using one of two methods:
If index ID is between 1 and 250, drop and re-create each nonclustered index on the table.
Or:
If a clustered index also exists on the table, drop and re-create it, which causes all nonclustered indexes to be automatically rebuilt. If your table is large, you may not have the space to do this (a rule of thumb is that 150 percent of your table size must be available).
If the corruption is not on an index page, run dbcc tablealloc or dbcc checkalloc on the corrupted object. The output should report OAM allocation errors. Try to fix the reported allocation errors by referring to the information in this manual.
If the 1142 errors persist, call Technical Support for assistance.
Before calling Technical Support, have the following information available:
Server version and SWR version level
Server error log
Text of all the error messages
Operating system error log
Output from dbcc page for the corrupted page
Output from dbcc tablealloc or dbcc checkalloc for the corrupted object
All versions
22
Invalid OAM Page %ld. Expected object=%ld. Found object=%ld, database=%d, pstat=0x%x, indid=%d.
Invalid OAM Page %ld. Expected object=%S_OBJID. Found Object=%S_OBJID,database=%S_DBID,pstat=%d, indid=%d.
This error occurs when the status on the page is not for an Object Allocation Map (OAM) page or the page is an OAM page and the object ID on the page does not match the object that the OAM page supposedly belongs to.
Error 1143 is a serious error and may result in reading the wrong OAM page into data cache and corrupting a buffer in the process.
Make a note of the object ID and index ID in the error message output.
If the corruption is on a user table's index (object ID is 100 or greater and index ID is between 1 and 250), run dbcc checktable to verify that the data page linkage is good. If checktable runs cleanly, dropping and re-creating the index should clear the corruption.
Use sp_helpindex to list all indexes on the table and then rebuild all the affected nonclustered indexes using one of two methods:
If index ID is between 1 and 250, drop and re-create each affected nonclustered index on the table.
Or:
If a clustered index also exists on the table, drop and re-create it, which causes all nonclustered indexes to be automatically rebuilt. If your table is large, you may not have the space to do this (a rule of thumb is that 150 percent of your table size must be available).
If the corruption is not on an index page, run dbcc tablealloc and/or dbcc checkalloc on the corrupted object. The output should report OAM allocation errors. Try to fix the reported errors first by referring to the appropriate writeups in this manual.
If the 1143 errors persist, call Sybase Technical Support for assistance.
Before calling Technical Support, have the following information available:
Server version and SWR version level
Server error log
Text of all the error messages
Operating system error log
Output from dbcc page for the corrupted page
Output from dbcc tablealloc or dbcc checkalloc for the object identified by dbcc page
All versions
