DBCC CHECKALLOC
DBCC stands for Database Console commands for SQL Server. This is widely used by the system and database administrators for maintenance, miscellaneous tasks, informational messages and validation of database, index catalog, and allocation pages.
I will go through the DBCC CHECKALLOC command which is widely used for checking the allocation/consistency failures.
Syntax:
DBCC CHECKALLOC (<database name>);
Example:
DBCC CHECKALLOC (AdventureWorks2012);
GO
In the below example, I didn’t face any allocation errors (or) consistency errors in the database ‘Adventureworks2012’.
DBCC results for 'AdventureWorks2012'.
***************************************************************
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). 23 pages used in 2 dedicated extents.
Total number of extents is 2.
***************************************************************
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:279). Dpages 0.
Index ID 1, partition ID 327680, alloc unit ID 327680 (type In-row data). 5 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.
***************************************************************
The total number of extents = 2904, used pages = 22527, and reserved pages = 23215 in this database.
(number of mixed extents = 94, mixed pages = 735) in this database.
CHECKALLOC found 0 allocation errors and 0 consistency errors in database 'AdventureWorks2012'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
If there are any allocation errors and consistency errors, then the below steps can be followed in rectifying the issues depending upon the situation. Microsoft recommends to use the DBCC CHECKDB without REPAIR_ALLOW_DATA_LOSS option. Full Backup of the database is recommended before running the DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS. This option should be used only as a last resort.
ALTER DATABASE AdventureWorks2012 set single_user with rollback immediate
DBCC CHECKDB (AdventureWorks2012, REPAIR_ALLOW_DATA_LOSS) with NO_INFOMSGS,ALL_ERRORMSGS
ALTER DATABASE AdventureWorks2012 set multi_user
There are other options which can be combined DBCC CHECKALLOC for checking the “tempdb” database usage.
DBCC CHECKALLOC (AdventureWorks2012, REPAIR_ALLOW_DATA_LOSS) with ESTIMATEONLY
GO
Estimated TEMPDB space (in KB) needed for CHECKALLOC on database AdventureWorks2012 = 646752.
CHECKALLOC found 0 allocation errors and 0 consistency errors in database 'AdventureWorks2012'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Total 646752 KB of TEMPDB space is used by CHECKALLOC database console command.
