Database Console Command MSSQL

It is also known as Database Consistency Checker. By using DBCC commands we can find  out what’s going on inside your SQL Server system. Database Consistency Checker Commands give details in form of statistics about the SQL Server. They can be used for maintenance of database, index, or filegroup. These commands are mostly used to check the consistency of database pages. All the database objects like tables,index,views.in the form of pages internally (on disk each page has in the form of 8KB chunks). DBCC Commands can be used to perform validation operation database object pages. They also can perform tasks such as enabling trace flags or removing a DLL from memory.

We can run the DBCC Commands in SQL SERVER MANAGEMENT STUDIO(SSMS) and to run that commands, the SQL SERVER login user should have “sysadmin” permission and also to execute some of the DBCC Commands we need to have “db_owner” permission for the login user.

We can know more undocumented DBCC commands by using the below command

DBCC TRACEON(2520)

DBCC HELP(‘?’)

If we execute these commands in SSMS it will print the DBCC Commands.

DBCC HELP command is also used find the syntax for other DBCC Commands. For  Example, DECLARE @dbcc_stmt sysname
SET @dbcc_stmt = ‘HELP’

DBCC HELP (@dbcc_stmt)
GO

Here sysname is a datatype. We can know the syntax for any DBCC command using above commands. We have to specify DBCC command name in @dbcc_stmt or else we can directly give like this DBCC HELP (‘COMMAND_NAME_HERE’’) it will give the syntax.

Before running DBCC commands for the most of the commands we should have to Set TRACE FALG 3604 ON.

The purpose of  DBCC TRACEON(3604)  is that some DBCC commands are designed to redirect the output to the log, attached debugger, or a trace listener.  This command simply instructs them to redirect the output to the client executing the command – most often SQL Server Management Studio (SSMS).  This command is only used for Session level.

To globally enable the TRACEON just we have to pass another parameter to that command

DBCC TRACEON(3604, –1)

Some of the important DBCC Commands

CHECKDB

DBCC CHECKDB checks the logical and physical integrity of the specified database which includes all the databases objects in the database.

DBCC CHECKALLOC, DBCC CHECKTABLE, and DBCC CHECKCATALOG  DBCC CHECKDB includes all the three command. Usage of these included DBCC commands is listed below.

DBCC CHECKALLOC – Checks the 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 specified database. The database must be online to run the DBCC command.

DBCC TRACEON(3604)

DBCC CHECKDB (‘Vembu’);
If we run the above command in SSMS it will give the result like below.

If we run the above command it will check the consistency  each page of every object in database. If the database is corrupted it will show the error which page contains error exactly it will show from the result we can solve the error.

The command execution speed depends on the size of the database,Load on the system,the configuration of tempdb, corruption that are found.

DBCC PAGE

This command is used to view the content of data pages.

DBCC PAGE

(