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.
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.
Syntax
DBCC CHECKALLOC
(
['database_name'|database_id|0]
[,NOINDEX
|
{REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
}
]
)
[WITH {[ALL_ERRORMSGS ]
[,NO_INFOMSGS ]
[,TABLOCK ]
[,ESTIMATEONLY ]
}
]
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.
Example
To check consistency disk space allocation structures for a database named mcn, write following code snipplet:
DBCC CHECKALLOC ('mcn')
Output:
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.
Working
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.