Skip to content

DBCC CheckAlloc

Being day one of DBBC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC CHECKALLOC.

Description:

DBCC CheckAlloc checks and can repair disk space allocation structures for a database. DBCC CheckAlloc checks the allocation for all pages in the database compared to their internal structures representing those pages. When you run DBCC CheckDB it internally runs the equivalent of DBCC CheckAlloc as part of the process.

DBCC CHECKALLOC Syntax:

dbcc checkalloc
(
     [ { 'database_name' | database_id | 0 } ]
     [ , NOINDEX |
     { REPAIR_ALLOW_DATA_LOSS
     | REPAIR_FAST
     | REPAIR_REBUILD
     } ]
)
     [ WITH
         {
             [ ALL_ERRORMSGS ]
           	 [ , [ NO_INFOMSGS ] ]
           	 [ , [ TABLOCK ] ]
             [ , [ ESTIMATEONLY ] ]
         }
     ]

DBCC CheckAlloc Example:

Running DBCC CheckAlloc without a parameter checks the current database. Alternatively the first parameter can specify a database name or database id.

-- Check the current database.
DBCC CHECKALLOC;

Which gives the following output:

DBCC results for 'cte_demo'.
***************************************************************
Table sys.sysrscols                Object ID 3.
Index ID 1, partition ID 196608, alloc unit ID 196608 (type In-row data). FirstIAM (1:157). Root (1:158). Dpages 12.
Index ID 1, partition ID 196608, alloc unit ID 196608 (type In-row data). 14 pages used in 1 dedicated extents.
Total number of extents is 1.
***************************************************************
Table sys.sysrowsets                Object ID 5.
Index ID 1, partition ID 327680, alloc unit ID 327680 (type In-row data). FirstIAM (1:131). Root (1:17). Dpages 1.
Index ID 1, partition ID 327680, alloc unit ID 327680 (type In-row data). 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sys.sysclones                Object ID 6.
Index ID 1, partition ID 281474977103872, alloc unit ID 281474977103872 (type In-row data). FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 1, partition ID 281474977103872, alloc unit ID 281474977103872 (type In-row data). 0 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sys.sysallocunits                Object ID 7.
Index ID 1, partition ID 458752, alloc unit ID 458752 (type In-row data). FirstIAM (1:21). Root (1:139). Dpages 0.
Index ID 1, partition ID 458752, alloc unit ID 458752 (type In-row data). 4 pages used in 0 dedicated extents.
Index ID 2, partition ID 562949953880064, alloc unit ID 562949953880064 (type In-row data). FirstIAM (1:18). Root (1:16). Dpages 1.
Index ID 2, partition ID 562949953880064, alloc unit ID 562949953880064 (type In-row data). 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
.
.
.
Dozens of rows removed
.
.
.
***************************************************************
Table sys.filetable_updates_2105058535                Object ID 2105058535.
Index ID 1, partition ID 72057594038976512, alloc unit ID 72057594043301888 (type In-row data). FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 1, partition ID 72057594038976512, alloc unit ID 72057594043301888 (type In-row data). 0 pages used in 0 dedicated extents.
Total number of extents is 0.
File 1. The number of extents = 35, used pages = 242, and reserved pages = 274.
           File 1 (number of mixed extents = 21, mixed pages = 162).
    Object ID 3, index ID 1, partition ID 196608, alloc unit ID 196608 (type In-row data), data extents 1, pages 14, mixed extent pages 9.
    Object ID 5, index ID 1, partition ID 327680, alloc unit ID 327680 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 7, index ID 1, partition ID 458752, alloc unit ID 458752 (type In-row data), data extents 0, pages 4, mixed extent pages 4.
    Object ID 7, index ID 2, partition ID 562949953880064, alloc unit ID 562949953880064 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 8, index ID 0, partition ID 524288, alloc unit ID 524288 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 20, index ID 1, partition ID 281474978021376, alloc unit ID 281474978021376 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 23, index ID 1, partition ID 281474978217984, alloc unit ID 281474978217984 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 24, index ID 1, partition ID 281474978283520, alloc unit ID 281474978283520 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 27, index ID 1, partition ID 281474978480128, alloc unit ID 281474978480128 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 27, index ID 2, partition ID 562949955190784, alloc unit ID 562949955190784 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 27, index ID 3, partition ID 844424931901440, alloc unit ID 844424931901440 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 29, index ID 1, partition ID 281474978611200, alloc unit ID 281474978611200 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 34, index ID 1, partition ID 281474978938880, alloc unit ID 281474978938880 (type In-row data), data extents 3, pages 31, mixed extent pages 9.
    Object ID 34, index ID 2, partition ID 562949955649536, alloc unit ID 562949955649536 (type In-row data), index extents 3, pages 26, mixed extent pages 9.
    Object ID 34, index ID 3, partition ID 844424932360192, alloc unit ID 844424932360192 (type In-row data), index extents 3, pages 26, mixed extent pages 9.
    Object ID 34, index ID 4, partition ID 1125899909070848, alloc unit ID 1125899909070848 (type In-row data), index extents 0, pages 7, mixed extent pages 7.
    Object ID 41, index ID 1, partition ID 281474979397632, alloc unit ID 281474979397632 (type In-row data), data extents 1, pages 13, mixed extent pages 9.
    Object ID 41, index ID 2, partition ID 562949956108288, alloc unit ID 562949956108288 (type In-row data), index extents 0, pages 7, mixed extent pages 7.
    Object ID 44, index ID 1, partition ID 281474979594240, alloc unit ID 281474979594240 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 44, index ID 2, partition ID 562949956304896, alloc unit ID 562949956304896 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 50, index ID 1, partition ID 281474979987456, alloc unit ID 281474979987456 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 50, index ID 2, partition ID 562949956698112, alloc unit ID 562949956698112 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 50, index ID 3, partition ID 844424933408768, alloc unit ID 844424933408768 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 54, index ID 1, partition ID 281474980249600, alloc unit ID 281474980249600 (type In-row data), data extents 0, pages 4, mixed extent pages 4.
    Object ID 54, index ID 2, partition ID 562949956960256, alloc unit ID 562949956960256 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 55, index ID 1, partition ID 281474980315136, alloc unit ID 281474980315136 (type In-row data), data extents 0, pages 4, mixed extent pages 4.
    Object ID 55, index ID 2, partition ID 562949957025792, alloc unit ID 562949957025792 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 58, index ID 1, partition ID 281474980511744, alloc unit ID 72057594037993472 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 58, index ID 2, partition ID 562949957222400, alloc unit ID 72057594038059008 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data), index extents 0, pages 5, mixed extent pages 5.
    Object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 281474980642816 (type In-row data), data extents 2, pages 18, mixed extent pages 9.
    Object ID 64, index ID 1, partition ID 281474980904960, alloc unit ID 281474980904960 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 64, index ID 2, partition ID 562949957615616, alloc unit ID 562949957615616 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 69, index ID 1, partition ID 281474981232640, alloc unit ID 72057594039697408 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 69, index ID 2, partition ID 562949957943296, alloc unit ID 72057594039762944 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 69, index ID 3, partition ID 844424934653952, alloc unit ID 72057594039828480 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 74, index ID 1, partition ID 281474981560320, alloc unit ID 281474981560320 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 74, index ID 2, partition ID 562949958270976, alloc unit ID 562949958270976 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 75, index ID 1, partition ID 281474981625856, alloc unit ID 281474981625856 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 75, index ID 2, partition ID 562949958336512, alloc unit ID 562949958336512 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 90, index ID 1, partition ID 281474982608896, alloc unit ID 72057594038583296 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 90, index ID 2, partition ID 562949959319552, alloc unit ID 72057594038648832 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 91, index ID 1, partition ID 281474982674432, alloc unit ID 72057594038779904 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 91, index ID 2, partition ID 562949959385088, alloc unit ID 72057594038845440 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 92, index ID 1, partition ID 281474982739968, alloc unit ID 72057594038976512 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 93, index ID 1, partition ID 281474982805504, alloc unit ID 72057594039107584 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 93, index ID 2, partition ID 562949959516160, alloc unit ID 72057594039173120 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 97, index ID 1, partition ID 281474983067648, alloc unit ID 72057594038190080 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
    Object ID 97, index ID 2, partition ID 562949959778304, alloc unit ID 72057594038255616 (type In-row data), index extents 0, pages 2, mixed extent pages 2.
    Object ID 99, index ID 0, partition ID 0, alloc unit ID 6488064 (type Unknown), index extents 1, pages 7, mixed extent pages 1.
    Object ID 245575913, index ID 0, partition ID 72057594039042048, alloc unit ID 72057594043432960 (type In-row data), data extents 0, pages 2, mixed extent pages 2.
The total number of extents = 35, used pages = 242, and reserved pages = 274 in this database.
       (number of mixed extents = 21, mixed pages = 162) in this database.
CHECKALLOC found 0 allocation errors and 0 consistency errors in database 'cte_demo'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If errors were encountered on the output, it states to contact your system administrator. But what if you are the system administrator or DBA… At that point your safest option is to restore from backup. The unsafe alternative is to run DBCC CheckAlloc with one of the repair options to attempt to fix your database corruption.

DBCC CheckAlloc Example with Corruption:

Here we will be using the dbcc_corruption database with a table named Departments. The IAM on the Departments table is corrupt, so DBCC_CheckAlloc produces the following errors:


USE [dbcc_corruption];
DBCC CheckAlloc();

DBCC results for ‘dbcc_corruption’.
Msg 8939, Level 16, State 98, Line 2
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 99360666784432128 (type

Unknown), page (1:272). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -6.
Msg 8928, Level 16, State 6, Line 2
Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown): Page (1:272) could
not be processed. See other errors for details.
Msg 8906, Level 16, State 1, Line 2
Page (1:272) in database ID 17 is allocated in the SGAM (1:3) and PFS (1:1), but was not
allocated in any IAM. PFS flags ‘IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL’.
CHECKALLOC found 3 allocation errors and 0 consistency errors not associated with any
single object.
***************************************************************
Table sys.sysrscols Object ID 3.
Index ID 1, partition ID 196608, alloc unit ID 196608 (type In-row data). FirstIAM (1:157).
Root (1:158). Dpages 12.
Index ID 1, partition ID 196608, alloc unit ID 196608 (type In-row data). 14 pages used in 1
dedicated extents.
Total number of extents is 1.
***************************************************************
Now to go about fixing it.  First I would recommend checking the contents of the table to see if it look correct, if the table contents don’t look right it may require to restore from backup:


SELECT * FROM Departments;

DBCC_CheckAllocCorrupt2

We see 4 rows which tells us that our table data is ok.  It might be a good idea at this point to copy the data from this table into another table. For now we will attempt to repair the table, so we run the DBCC CheckAlloc with REPAIR_REBUILD option.  Keep in mind that DBCC CheckAlloc must be run in single user mode.


ALTER DATABASE [dbcc_corruption] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CheckAlloc(dbcc_corruption, REPAIR_REBUILD);
ALTER DATABASE [dbcc_corruption] SET MULTI_USER WITH ROLLBACK IMMEDIATE;

DBCC results for ‘dbcc_corruption’.
Msg 8928, Level 16, State 6, Line 2
Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown): Page (1:272) could

not be processed. See other errors for details.
Repairing this error requires other errors to be corrected first.
Msg 8906, Level 16, State 1, Line 2
Page (1:272) in database ID 17 is allocated in the SGAM (1:3) and PFS (1:1), but was not

allocated in any IAM. PFS flags ‘IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL’.
The repair level on the DBCC statement caused this repair to be bypassed.
Msg 8939, Level 16, State 98, Line 2
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 99360666784432128 (type

Unknown), page (1:272). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 2057 and -6.
Repairing this error requires other errors to be corrected first.
CHECKALLOC found 3 allocation errors and 0 consistency errors not associated with any

single object.
***************************************************************
Indicating failure, the REPAIR_REBUILD option for DBCC CheckAlloc won’t do the job.  Now for the REPAIR_ALLOW_DATA_LOSS option. You should have copied the contents of this table into another table before trying this.


ALTER DATABASE [dbcc_corruption] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CheckAlloc(dbcc_corruption, REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE [dbcc_corruption] SET MULTI_USER WITH ROLLBACK IMMEDIATE;

DBCC results for ‘dbcc_corruption’.
Repair: The page (1:272) has been deallocated from object ID 0, index ID -1,
partition ID 0, alloc unit ID 99360666784432128 (type Unknown).
Msg 8928, Level 16, State 6, Line 2
Object ID 0, index ID -1, partition ID 0, alloc
unit ID 0 (type Unknown): Page (1:272) could not be processed. See other
errors for details.
 The error has been repaired.
Msg 8906, Level 16, State 1, Line 2
Page (1:272) in database ID 17 is allocated
in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags
‘IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL’.
 The error has been repaired.
Msg 8939, Level 16, State 98, Line 2
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 99360666784432128
(type Unknown), page (1:272). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 2057 and -6.
 The error has been repaired.
CHECKALLOC found 3 allocation errors and 0 consistency errors not associated with any
single object.
CHECKALLOC fixed 3 allocation errors and 0 consistency errors not associated with any
single object.
***************************************************************
This time the error is repaired with DBCC CheckAlloc. But now for the real test, does our table data look alright?

SELECT * FROM Departments;

DBCC_CheckAllocCorrupt3
Which looks good. Now to run DBCC CheckAlloc to see how things look.

USE [dbcc_corruption];
DBCC CheckAlloc();

…. output truncated
The total number of extents = 35, used pages = 244, and reserved pages =
275 in this database. (number of mixed extents = 21, mixed pages = 163)
in this database.
CHECKALLOC found 0 allocation errors and 0 consistency errors in database
‘dbcc_corruption’.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Here we see that the fix worked, and the table is fine. The next step after DBCC CheckAlloc would be to run DBCC CheckDB to see if the rest of the database is fine also. In this case the rest of the database is fine.

DBCC CheckAlloc Notes:

DBCC CheckAlloc along with DBCC CheckTable for every object in the database are called when DBCC CheckDB is run. Running DBCC CheckAlloc or DBCC CheckTable would be redundant after running DBCC CheckDB.

For more information see TSQL Wiki DBCC checkalloc.

DBCC Command month at SteveStedman.com is almost as much fun as shark week.

More from Stedman Solutions:

SteveStedman5
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *