SQL Server 2012: DBCC Commands: DBCC CHECKALLOC

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.

Attach and Detach of Databases in SQL Server

I have seen some of general queries regarding attach and detach of databases in SQL server (MSDN) forums. This inspired me to write this blog, so that it will be useful for others as well.

Let me also give some background of attach and detach is used generally. This is used as one of the option in moving databases from one SQL server database to another.

Attach and Detach of databases can be done in three methods (SQL Server Management Studio, System Stored Procedures, Using Create database command) in SQL server.

1. Using SQL Server Management Studio is simple and straight forward method which can be done by right     clicking on database and selecting attach method and then by adding the mdf file from operating system location.

2. System stored procedures sp_attach_db and sp_detach_db can be used for attaching and detaching the databases. Only 16 files can be attached by using this method. This is method is not supporting in future versions of SQL server. Microsoft recommends to use CREATE DATABASE method.

Set the database in single user mode

ALTER DATABASE AdventureWorks2012_1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO

Issue the detach mode command

EXEC SP_DETACH_DB @DBNAME=N'ADVENTUREWORKS2012_1'
GO

EXEC SP_ATTACH_DB @dbname = N'AdventureWorks2012_1',
@filename1 ='C:\TEST\AdventureWorks2012_Data.MDF'
GO

3. CREATE DATABASE method which will the option supported in future versions of SQL Server. In this example, I don't have log (LDF) file to attach to the database, hence I have use "ATTACH_REBUILD_LOG" option. You can use "FOR ATTACH" option if you have all required files for the attach.

USE MASTER

GO

CREATE DATABASE ADVENTUREWORKS2012 (FILENAME='C:\Adventureworks_2012\AdventureWorks2012_Data.MDF' ) FOR ATTACH_REBUILD_LOG

GO

Message after attach the database:

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Log.ldf" may be incorrect. New log file 'C:\Adventureworks_2012\ADVENTUREWORKS2012_log.ldf' was created Converting database 'ADVENTUREWORKS2012' from version 705 to the current version 706. Database 'ADVENTUREWORKS2012' running the upgrade step from version 705 to version 706.

What is error 15281 during distributor configuration in SQL server 2012 replication?

Error:

SQL server blocked access to procedure 'dbo_sp_set_sqlagent_properties of component 'Agent XPs' because this component is turned off as part of security configuration for this server. A system
administrator can enable the use use of 'Agent Xps' by using sp_configure.

Solution:

Configure the 'Agent XPs' using 'sp_configure'

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
 RECONFIGURE
 GO