INF: Using DBCC FIX_AL

• Microsoft SQL Server, version 4.2
• Microsoft SQL Server version 4.2 for OS/2

SUMMARY

The DBCC FIX_AL command is documented in the SQL Server "Troubleshooting Guide" on page 57. This article provides more information about running this command.

MORE INFORMATION

The DBCC FIX_AL command (short for "fix allocation") can be used to fix 2540 and 2521 errors detected by DBCC CHECKALLOC or DBCC NEWALLOC. Run DBCC CHECKDB and check to ensure that no other errors exist before running the FIX_AL command. The database must be in single user mode before running the FIX_AL command, and you should run this command from a database other than the one being fixed.

The DBCC FIX_AL command makes the allocation bitmaps match the actual data or index page chains. If there are any errors in the page chains, you should not run the FIX_AL command because it will try to rebuild the allocation bitmaps according to the information from the damaged chains.

The FIX_AL command will run the equivalent of a DBCC NEWALLOC to gather the necessary information to rebuild the bitmaps, and to try to ensure that no other errors will prevent it from running. After this, it will rebuild the bitmaps for all pages in the database.

The following is a script that could be used to run FIX_AL against the "pubs" database:

   use master
   go
   sp_dboption pubs, single, true
   go
   use pubs
   go
   checkpoint
   go
   use master
   go
   dbcc fix_al(pubs)
   go
   sp_dboption pubs, single, false
   go
   use pubs
   go
   checkpoint
   go