Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
When the database is created or manually increased in size or when it grows with auto growth, it allocates a space over the disk. With the DBCC CHECKALLOC command, a consistency test is performed in this assigned space.
You can use it as follows.
1 | DBCC CHECKALLOC (N'ASPNETDB') WITH NO_INFOMSGS, ALL_ERRORMSGS; |
You can fix problems with REPAIR_ALLOW_DATA_LOSS that is described in “DBCC CHECKDB Command On SQL Server” and “How To Recover Suspect Database in SQL Server“, but the REPAIR_REBUILD operation cannot be used with this command.
NOTE: After running DBCC CHECKALLOC with REPAIR_ALLOW_DATA_LOSS, I recommend that you run the DBCC CHECKCONSTRAINTS command. You can find information about this command in my article “DBCC CHECKCONSTRAINTS Command On SQL Server“.
Even if you don’t write ALL_ERRORMSGS at the end of the script, it accepts it by default. It means to show all errors received for each object.
“NO_INFOMSGS” at the end of the script means that it will displays information messages.
DBCC CHECKALLOC uses internal database snapshot, such as DBCC CHECKDB. You may want to read the article “What is Database Snapshot On SQL Server“.
If you run DBCC CHECKALLOC using the following command, it puts exclusive lock on the database instead of using “internal database snapshot”. You may want to read my article “SQL Server Lock Types“.
If you run the DBCC CHECKDB and DBCC CHECKFILEGROUP commands, you do not need to run DBCC CHECKALLOC. You can find details of the related commands in my articles below.
“DBCC CHECKDB Command On SQL Server“,
“DBCC CHECKFILEGROUP Command On SQL Server”
As an important detail, you cannot run this command in the tempdb database. and the consistency of the filestream data is not checked with this command. You can find information about File Stream in my article “What is File Stream On SQL Server“.
