DBCC/DMV

1. What is back process for the DBCC?

A:

When you execute one of these DBCC commands, the Database Engine creates a database snapshot and brings it to a transactionally consistent state. The DBCC command then runs the checks against this snapshot. After the DBCC command is completed, this snapshot is dropped.

Sometimes an internal database snapshot is not required or cannot be created. When this occurs, the DBCC command executes against the actual database. If the database is online, the DBCC command uses table-locking to ensure the consistency of the objects that it is checking. This behavior is the same as if the WITH TABLOCK option were specified.

An internal database snapshot is not created when a DBCC command is executed:

·         Against master, and the instance of SQL Server is running in single-user mode.

·         Against a database other than master, but the database has been put in single-user mode by using the ALTER DATABASE statement.

·         Against a read-only database.

·         Against a database that has been set in emergency mode by using the ALTER DATABASE statement.

·         Against tempdb. In this case, a database snapshot cannot be created because of internal restrictions.

·         Using the WITH TABLOCK option. In this case, DBCC honors the request by not creating a database snapshot.

2. What is the use of DBCC commands?

DBCC stands for database consistency checker. There are many DBCC command in SQL Server. We generally use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.

3. DBCC Commands?

Informational Statements
DBCC INPUTBUFFER	DBCC SHOWCONTIG
DBCC OPENTRAN	DBCC SQLPERF
DBCC OUTPUTBUFFER	DBCC TRACESTATUS
DBCC PROCCACHE	DBCC USEROPTIONS
DBCC SHOW_STATISTICS
Validation Statements
DBCC CHECKALLOC	DBCC CHECKFILEGROUP
DBCC CHECKCATALOG	DBCC CHECKIDENT
DBCC CHECKCONSTRAINTS	DBCC CHECKTABLE
DBCC CHECKDB
Maintenance Statements
DBCC CLEANTABLE	DBCC INDEXDEFRAG
DBCC DBREINDEX	DBCC SHRINKDATABASE
DBCC DROPCLEANBUFFERS	DBCC SHRINKFILE
DBCC FREEPROCCACHE	DBCC UPDATEUSAGE
Miscellaneous Statements
DBCC dllname (FREE)	DBCC HELP
DBCC FREESESSIONCACHE	DBCC TRACEOFF
DBCC FREESYSTEMCACHE	DBCC TRACEON

4. What are the DMV's in SQL Server ?

Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.

Important

Dynamic management views and functions return internal, implementation-specific state data. Their schemas and the data they return may change in future releases of SQL Server. Therefore, dynamic management views and functions in future releases may not be compatible with the dynamic management views and functions in this release. For example, in future releases of SQL Server, Microsoft may augment the definition of any dynamic management view by adding columns to the end of the column list. We recommend against using the syntax SELECT * FROM dynamic_management_view_name in production code because the number of columns returned might change and break your application.

5. How to resolve Allocation and consistency Errors?

Run DBCC checkdb DBName

Repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (SomeDB).

8. what is the exact use of DMVs?

DMVs can be used in the gathering of baseline information and for diagnosing performance problems. Few important dmvs are:
1. sys.dm_os_performance_counters
2. sys.dm_db_index_physical_stats
3. sys.dm_db_index_usage_stats

9. Can explain DBCC background process?

DBCC CHECKDB is a Algorithm , backend checks that:

1. Object Integrity
2. Linkages for text, ntext, and image pages
3. Index and data pages are correctly linked.
4. Indexes are in their proper sort order.
5. Pointers are consistent.
6. The data on each page is reasonable (Allocation Checks).
7. Page offsets are reasonable.

10. DMV Categories?

DMV Categories

Category	Prefix
Common Language Runtime (CLR)	Sys.dm_clr_*
Database	Sys.dm_db_*
Indexing	Sys.dm_db_index_*
Database Mirroring	Sys.dm_db_mirroring_*
Execution	Sys.dm_exec_*
Full-Text Search	Sys.dm_fts_*
I/O	Sys.dm_io_*
Query Notifications	Sys.dm_qn_*
Replication	Sys.dm_repl_*
Service Broker	Sys.dm_broker_*
SQL Server Operating System	Sys.dm_os_*
Transactions	Sys.dm_tran_*
Change Data Capture	Sys.dm_cdc_*
Object	Sys.dm_sql_*
Resource Governor	Sys.dm_resource_governor_*
SQL Server Extended Events	Sys.dm_xe_*
	Sys.dm_cryptographic_*
Security	Sys.dm_provider_*
	Sys.dm_audit_*

11. What is use of DBCC Commands?

DBCC (Database consistency checker) act as Database console commands for SQL Server to check database consistency. They are grouped as: Maintenance: Maintenance tasks on Db, filegroup, index etc. Commands include DBCC CLEANTABLE, DBCC INDEXDEFRAG, DBCC DBREINDEX, DBCC SHRINKDATABASE, DBCC DROPCLEANBUFFERS, DBCC SHRINKFILE, DBCC FREEPROCCACHE, and DBCC UPDATEUSAGE.

Miscellaneous: Tasks such as enabling tracing, removing dll from memory. Commands include DBCC dllname, DBCC HELP, DBCC FREESESSIONCACHE, DBCC TRACEOFF, DBCC FREESYSTEMCACHE, and DBCC TRACEON. Informational: Tasks which gather and display various types of information. Commands include DBCC INPUTBUFFER, DBCC SHOWCONTIG, DBCC OPENTRAN, DBCC SQLPERF, DBCC OUTPUTBUFFER, DBCC TRACESTATUS, DBCC PROCCACHE, DBCC USEROPTIONS, and DBCC SHOW_STATISTICS.

Validation: Operations for validating on Db, index, table etc. Commands include DBCC CHECKALLOC, DBCC CHECKFILEGROUP, DBCC CHECKCATALOG, DBCC CHECKIDENT, DBCC CHECKCONSTRAINTS, DBCC CHECKTABLE, and DBCC CHECKDB.

12.