Error 2540

Severity

16

Message text

Versions earlier than 15.0:

Table Corrupt: Page is allocated but not linked; check the following pages and ids: allocation pg#=%ld extent id=%ld logical pg#=%ld object id on extent=%ld (object name = %S_OBJID) indid on extent=%ld

Version 15.0 and later:

NoteThe following message that displays indicates which dbcc command to run to correct the error.

Table Corrupt: Page is allocated but not linked. Run DBCC %s to correct the problem. (allocation page#=%ld, extent id=%ld, logical page#=%ld, object id in extent=%ld, object name=%S_OBJID, index id in extent=%ld)

Explanation

This error occurs when dbcc checkalloc determines that a page is marked as allocated to an object but that page is not being used. There is no corruption or data loss associated with this error.

Each 2540 error means the loss of one blank data page. A few 2540 errors are no cause for concern. However, if many of these errors occur, the amount of “lost” disk space could be significant.

NoteThe instructions that follow are for fixing 2540 errors once they have occurred. Two easy-to-use strategies exist for detecting this error earlier. Refer to “Fixing and preventing allocation errors” and “Detecting allocation errors early” in the chapter “Other Useful Tasks” in the most recent version of the Troubleshooting and Disaster Recovery guide for information.

Action

Occasionally dbcc checkalloc reports this error when no real error condition exists. You can either check to determine whether the error is real, or continue with this section and take action to correct it, whether or not it reflects a real allocation error.

Because the process used to discover whether or not the error is real can be time-consuming, you can go directly to “Error resolution”.


Verifying that the error is real

Run dbcc checkalloc in single-user mode if you suspect the 2540 error messages are incorrect. If the error is in master, use the instructions in “Starting Adaptive Server in single-user mode” in the chapter “Server Recovery Tasks” in the most recent version of Troubleshooting and Disaster Recovery to correct the error. Refer to “dbcc” in the Reference Manual: Commands for information about dbcc checkalloc.


Error resolution

If these errors persist, it is possible to clear them all at once by using the dbcc checkalloc and dbcc checkalloc with fix option commands. Refer to “Fixing and preventing allocation errors” in the chapter “Other Useful Tasks” in the most recent version of the Troubleshooting and Disaster Recovery guide for information about using dbcc checkalloc.

If the text of the error message includes a real object name, not a number, then the error is on an existing object to which the system catalog has correct references, and you should continue to “Identify table: User or system table”.

If a number appears instead of the object name, then that object only partially exists and the error must be corrected using the procedure described in “Fixing and preventing allocation errors” in the chapter “Other Useful Tasks” in the most recent version of the Troubleshooting and Disaster Recovery.


Identify table: User or system table

Look at the value for “object id on extent” in the error message. If it is 100 or greater, continue with “Action for user tables.” If the “object id on extent” is less than 100, it is a system table and requires the procedure “Action for system tables”.


Action for user tables

If the “object id on extent” in the error message is 100 or greater, follow these steps to correct the error:

  1. Check the value of the “indid on extent” in the error message to determine whether it is a table (value = 0) or an index (value > 0).

  2. Run dbcc tablealloc or dbcc indexalloc, depending on whether the object named in the 2540 error message is a table or an index. Before you run the appropriate command, keep in mind:

    • dbcc tablealloc corrects this problem on a table or an index, but if the problem is on an index, use dbcc indexalloc to avoid affecting the entire table. If the table is large or heavily used, it may be more practical to use dbcc indexalloc.

    • These commands can correct the error only when run in full or optimized mode and with the nofix option not specified (the default for user tables).

    • Use the object name or object ID in the following commands where the argument <object_name> appears.

Use the command appropriate for your situation:

Tables (index ID in extent = 0)

Indexes (0 < index ID in extent < 255)

1> dbcc tablealloc (<object_name>) 2> go

1> dbcc indexalloc (<object_name>, 2> <indid_on_extent>) 3> go

Refer to “dbcc” in the Reference Manual: Commands and “Checking Database Consistency” in the System Administration Guide: Volume 2 for information about dbcc tablealloc and dbcc indexalloc.


Action for system tables

If the “object id on extent” in the error message is less than 100, follow these steps to correct the error:

  1. Put the affected database in single-user mode:

    • If the database is master, use the procedure in “Starting Adaptive Server in single-user mode” in the chapter “Server Recovery Tasks” in the most recent version of Troubleshooting and Disaster Recovery, 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
      
  2. Check the value of the “indid on extent” in the error message to determine whether it is a table (value is 0) or an index (value is greater than 0).

  3. Run dbcc tablealloc or dbcc indexalloc, depending on whether the object named in the 2540 error message is a table or an index. Then execute the appropriate command. Before you run the appropriate command, keep the following 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. To minimize the amount of time the table is unavailable, 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 either the “object name in extent” or “object id” values from the error message in the commands where the argument <object_name> appears.

    Use the command appropriate for your situation:

    Tables (index ID in extent = 0)

    Indexes (0 < index ID in extent < 255)

    1> dbcc tablealloc (object_name, 2> full, fix) 3> go

    1> dbcc indexalloc (object_name, indid_on_extent, 2> full, fix) 3> go

  4. Turn off single-user mode in the database:

    • If the database is master, refer to “Returning Adaptive Server to multiuser mode” in the chapter “Server Recovery Tasks” in the most recent version of Troubleshooting and Disaster Recovery.

    • If the database is not master, use this 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: Commands and “Checking Database Consistency” in the System Administration Guide: Volume 2 for information about dbcc tablealloc and dbcc indexalloc.

Versions in which this error is raised

All versions