Disk Space Allocation Structures Consistency In SQL Server 2008

In this article you will learn how to check consistency of Disk Allocation Structures using DBCC CHECKALLOC command.
  • 2020

In this article we will learn how to check consistency of Disk Allocation Structures using DBCC CHECKALLOC command. DBCC CHECKALLOC command checks the page usage and allocation in specified database. It also reports, if any error is found.



















Here, 'database_name' | database_id | 0 specifies database name or database id to check error. 0 indicates current database, NOINDEX specifies non clustered indexes are not checked, REPAIR_ALLOW_DATA_LOSS allows repair of tables or indexed views even there is a risk of data loss in the process, REPAIR_FAST | REPAIR_REBUILD are managed for backword-compatibility, ALL_ERRORMSGS is used when we want to display each error to be displayed, if this parameter is not specified then maximum of 200 messages can be displayed, NO_INFOMSGS denotes all the informational messages, ESTIMATEONLY allows the space required by tempdb database.


To check consistency  disk space allocation structures for a database named mcn, write following code snipplet:




This output provides information about pages used and extents for each index. Check the final line of output, it represents the number of allocation errors occurred.


As I explained above, DBCC CHECKALLOC command is used to check the page usage and internal structure in the mcn database. When DBCC CHECKALLOC is executed, internal database snapshot is created for maintaining  transactional consistency in process. This returns internal page information, number of extents, and pages. Final line represents the allocation errors occurred. So, to maintain concurrency in your database, internal database snapshot must be created.


More Articles

© 2020 DotNetHeaven. All rights reserved.