Maintenance and Monitoring - Microsoft SQL Server 2008 R2 Administration - page 344

1. The first step is checking the page usage and allocation. Use DBCC CHECKALLOC
statement as follows:
-- Check the current database.
DBCC CHECKALLOC;
GO
-- Check the specific database.
DBCC CHECKALLOC (AdventureWorks2008R2);
GO
--Just an estimation and no elaborated messages
DBCC CHECKALLOC WITH ESTIMATEONLY,NO_INFOMSGS

2. When we execute the CHECKALLOC using ESTIMATEONLY and NO_INFOMSGS, the

result will be as follows:
Estimated TEMPDB space needed for CHECKALLOC (KB)
-------------------------------------------------
268
(1 row(s) affected)

3. Running DBCC CHECKALLOC on the frequently updated table will help integrity
verification and good maintenance practice.

4. Now, let us dive deep into checking the allocation and page structural integrity using

DBCC CHECKDB DBCC and CHECKFILEGROUP statements.

The DBCC CHECKALLOC functionality is included
in DBCC CHECKDB , so we do not have to perform
CHECKALLOC separately when CHECKDB is executed.

5. Execute the following TSQL statements:

--CHECK THE DATABASE ALLOCATION and PAGE STRUCTURE integrity
-- Check the current database.
DBCC CHECKDB;
GO
-- Check the AdventureWorks2008R2 database without
--nonclustered indexes and extended logical checks.
DBCC CHECKDB (AdventureWorks2008R2, NOINDEX) WITH EXTENDED_
LOGICAL_CHECKS;
GO

--Extended Logical Checks and Physical Only cannot be used
together
--with physical only
DBCC CHECKDB (AdventureWorks2008R2, NOINDEX) WITH PHYSICAL_ONLY;
GO