DBCC (Database Consistency Check)

DBCC is very important command to check for specific Server/Database physical or logical information.

we have many DBCC commands, we use them as per the requirement and information we need.

some of them are:
1. DBCC INPUTBUFFER(SPID) ---> Where SPID is an integer values which indicate to specific sql server command reunning behind it.
Syntex:
DBCC INPUTBUFFER(77)

Result in Column EventInfo:
select TOP 10 * from sys.objects

The alternative for DBCC INPUTBUFFER is fn_get_sql(sql_handle) which require and sql_handle of that specific spid
Syntex:
First we need to find out sql handle of that spid(77) using below command then we need to pass that sql_handle value to fn_get_sql funciton.
select sql_handle from sys.sysprocesses where spid = 77
select * from ::fn_get_sql(0x01001E0060C9581A40622EB90000000000000000)
Reslut in Column text
select TOP 10 * from sys.objects


SOME OTHER COMMANDS ARE:


2. DBCC CHECKDB ----> Checks the logical and physical integrity of all the objects in the specified database by performing the following operations:

i. DBCC CHECKALLOCK
ii. DBCC CHECKTABLE
iii. DBCC CHECKCATALOG
iv: Validates the contents of every indexed view in the database.
v: Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in
the file system using FILESTREAM.
vi: Validates the Service Broker data in the database.

CHECKDB with some Other options (a) DBCC CHECKDB (AdventureWorks2012, NOINDEX);(b) DBCC CHECKDB WITH NO_INFOMSGS; DBCC CHECKDB ('DB Name') WITH NO_INFOMSGS

3. DBCC useroptions ---> Returns the SET options active (set) for the current connection.
4. DBCC SHOW_STATISTICS('tbl_Employee', 'IX_tbl_Employee_EMPID') ---> to check the statistics info of index on a table
5. DBCC SHOWCONTIG ---> to check fragmentation information
6. DBCC OUTPUTBUFFER (71) ---> Input paramter is @@SPID , this command Returns the current output buffer in hexadecimal and ASCII
format for the specified session_id
7. DBCC PROCCACHE ---> Displays information in a table format about the procedure cache.
8. DBCC SQLPERF(LOGSPACE) ---> Displays Log File information of all available database on a given server
Output of this command look like:
Database Name Log Size(MB) Log Space Used (%) Status
TestDB 7.429688 16.41693 0
9. DBCC TRACESTATUS(-1); ---> Displays the status of trace flags.
10. DBCC OPENTRAN: ---> Displays information about the oldest active transaction and the oldest distributed and
nondistributed replicated transactions

There are other DBCC commands also we use these commands as and when required depends on the Usability and requirement.