performance and troubleshooting.
The DBCC Commands are used in Maintenance, Informational use, Validation on a
database, index, or file group. Theses commands are helpful to check physical
and logical consistency of database. Some DBCC statements fix errors
automatically. Here I am showing some commands that are frequently used.
Command | Description | Level | When to Check |
DBCC SQLPERF(logspace) | --To see transaction log size of each database on Server. | Server Level | Weekly |
DBCC SHOWFILESTATS | --Show Total Extents and used extents for database | Database Level | Weekly |
DBCC CHECKCATALOG | --Checks for catalog/tables consistency within the specified database | Database Level | Weekly |
DBCC CHECKCONSTRAINTS | --Checks the integrity of a specified constraint or all constraints on a specified table in the current database | Database Level | When you suspect that there are rows in your tables that do not meet the constraints/rules. |
DBCC CHECKALLOC | -- checks page usage and allocation in the database. | Database Level | if allocation errors are found for the database |
DBCC CHECKTABLE(tablename) | --It verifies index and data page links, index sort order, page pointers, index pointers, data page integrity, and page offsets on table. | Table level | Whenever required. |
DBCC CHECKIDENT(tablename) | --Checking identity information,return current identity value of specified table | Table Level | Whenever required. |
DBCC DBREINDEX(tablename) | -- rebuilds an index for a table or all indexes defined for a table. | Table level | (should not used rather use ALTER INDEX command) |
DBCC INDEXDEFRAG (databasename, tablename,indexname) | --defragment clustered and nonclustered indexes on tables and views | Table/View Level | (Should not use, Replacement is ALTER INDEX ... REORGANISE ) |
SELECT request_id FROM sys.dm_exec_requests WHERE session_id = @@spid; | --returns request_id | ||
DBCC INPUTBUFFER(sessionid) | --to view the last statement sent by the client connection to SQL Server | Database level | Whenever required |
DBCC SHRINKDATABASE(databasename) | --Shrinks the size of the data and log files in the specified database | Database Level | Avoid executing this command during busy periods in production |
DBCC SHRINKFILE(file_id) Use exec sp_helpfile to know filename,fileid,filegroup,size | --allows you to shrink the size of individual data and log files | Database level | Avoid, as in most cases the database will just regrow and shrinking data files causes fragmentation. |
DBCC TRACEOFF | --used to disable tracing | Server Level | |
DBCC TRACEON | --used to enable tracing | Server Level | |
DBCC TRACESTATUS | --used to know trace status with TraceFlag,Status,Global,Session | Server Level | |
DBCC USEROPTIONS | --Returns the SET options active (set) for the current connection | Server Level | Whenever required |
