list of dbcc commands in sql server

       Concept and basics of DBCC Commands in SQL Server

What is DBCC?

Database Consistency Checker will help us to check the consistency of the database, this is also known as database console commands.

DBCC SQLPERF:

• Provides Transaction log space usage for all log files on an instance
• Used to clear out data related to weight statistics, latch statistics, or spinlock statistics

·         -- clear spinlock statistics: 

·         DBCC SQLPERF ("sys.dm_os_spinlock_stats", CLEAR); 

·           

·         -- clear latch statistics: 

·         DBCC SQLPERF ("sys.dm_os_latch_stats", CLEAR); 

·           

·         --  clear wait statistics  

·         DBCC SQLPERF ("sys.dm_os_wait_stats", CLEAR); 

 

 

DBCC CHECKDB

·         Checks logical and physical integrity of all the objects in the specified database by performing the following operations:

DBCC CHECKALLOC: - Checks consistency of disk space allocation structures for a      

                                         Specified database.

DBCC CHECKTABLE: - Checks the integrity of all the pages and structures that make  

                                         Up the table or indexed view.

DBCC CHECKCATALOG: - Checks for catalog consistency within the database.

 

 

DBCC SHRINKFILE

use BANSH_DC

SELECT DB_NAME() AS DbName,

name AS FileName,

size/128.0 AS CurrentSizeMB,

size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB

FROM sys.database_files

WHERE type_desc = 'ROWS'

GO

 

GO

DBCC SHRINKFILE (N'BANSH_DC', 3, TRUNCATEONLY)

GO

 

DBCC SHOW_STATISTICS

DBCC SHOW_STATISTICS shows the actual status for a given statistic on a table or indexed view

*DBCC LOGINFO

 

Getting information about SQL Server virtual logs using DBCC LOGINFO

DBCC OPENTRAN

 

Open transactions in your transaction log that have not completed or have not been committed

No active open transactions.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

DBCC DROPCLEANBUFFERS

This commands remove the all the data from sql server cache (buffer)

 

DBCC ERRORLOG

DBCC CHECKALLOC

 DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

 

 

SELECT * FROM SYS.dm_os_sys_info

SELECT * FROM SYS.dm_os_hosts

SELECT * FROM SYS.dm_os_schedulerss

SELECT * FROM SYS.dm_io_pending_io_requests

 

select * from sys.dm_db_index_usage_stats

 

select * from  sys.dm_exec_connections

select * from  sys.dm_exec_requests

select * from  sys.dm_exec_sql_text