Shivprasad Koirala
SQL Server interview questions: - Explain DBCC?
By Shivprasad Koirala in ASP.NET on Jul 18 2011
  • Vishal Jadav
    Sep, 2016 12

    Its Database consistency check command. It can be used to find errors in db as well as repair db.

    • 0
  • Vilas Gite
    Aug, 2011 10


    At the time of table or table index fragmented heavily that time we will scan and find out status of table or table index by DBCC SHOWCONTIG command. Table fragmentation (Fragmentation means joining together) occurs when a large number of insert and update operations are performed on table.



    • 0
  • Shivprasad Koirala
    Jul, 2011 18

    DBCC (Database Consistency Checker Commands) is used to check logical and physical consistency of database structure.DBCC statements can fix and detect problems. These statements are grouped in to four categories:-

    • Maintenance commands like DBCC DBREINDEX, DBCC DBREPAR etc, they are mainly used for maintenance tasks in SQL SERVER.

    • Miscellaneous commands like DBCC ROWLOCK, DBCC TRACEO etc, they are mainly used for enabling row-level locking or removing DLL from memory.

    • Status Commands like DBCC OPENTRAN, DBCC SHOWCONTIG etc , they are mainly used for checking status of the database.

    • Validation Commands like DBCC CHECKALLOC, DBCCCHECKCATALOG etc , they perform validation operations on database.

    Note :- Check MSDN for list of all DBCC commands, it is very much possible specially during 
    DBA interviews they can ask in depth individual commands.

    Below is a sample screen in which DBCC SHOWCONTIG command is run. DBCC SHOWCONTIG is used to display fragmentation information for the data and indexes of the specified table.In the sample screen “Customer” table is checked for fragmentation

    DBCC SHOWCONTIG command in action.

    Fragmentation information. If “Scan density” is 100 then everything is contigious.The above image has scan density of 95.36% which is decent percentage. So such type of useful information can be collected by DBCC command and database performance and maintenance can be improved.


    • 0

Most Popular Job Functions