📜 Post with us on Reddit
📝 Create a new article
🏭 Create a company page
🇬🇧 - in English
🇫🇷 - en Français (FR)
🇵🇹 - em Português (PT)
🇩🇪 - in Deutsch (DE)
🇯🇵 - 日本語で (JA)
Sponsored articles

You can edit almost every page by Creating an account. Otherwise, see the FAQ.

Database Console Commands (Transact-SQL)

From EverybodyWiki Bios & Wiki


The Database Console Commands (DBCC) are a series of statements in Transact-SQL programming language to check the physical and logical consistency of a Microsoft SQL Server database.[1] These commands are also used to fix existing issues.[1] They are also used for administration and file management.[2]

DBCC was previously expanded as Database Consistency Checker.[3]

Categories of DBCC Commands[edit]

Based on their uses, DBCC commands are made of three categories of statements. They are:

Category Uses Commands
Maintenance statements Maintenance tasks DBCC DBREINDEX, DBCC DBREPAIR, DBCC INDEXDEFRAG, DBCC SHRINKDATABASE, DBCC SHRINKFILE, DBCC UPDATEUSAGE, DBCC CLEANTABLE, DBCC DROPCLEANBUFFERS, DBCC FREEPROCCACHE
Status statements Status checks DBCC INPUTBUFFER, DBCC OPENTRAN, DBCC OUTPUTBUFFER, DBCC PROCCACHE, DBCC SHOWCONTIG, DBCC SHOW_STATISTICS, DBCC SQLPERF, DBCC TRACESTATUS, DBCC USEROPTIONS
Validation statements Validation operations on a database and database components such as table, index, file catalog, etc.[1] DBCC CHECKALLOC, DBCC CHECKCATALOG, DBCC CHECKCONSTRAINTS, DBCC CHECKDB, DBCC CHECKFILEGROUP, DBCC CHECKIDENT, DBCC CHECKTABLE, DBCC NEWALLOC
Miscellaneous statements Miscellaneous tasks DBCC dllname (FREE), DBCC HELP, DBCC PINTABLE, DBCC ROWLOCK, DBCC TRACEOFF, DBCC TRACEON, DBCC UNPINTABLE
Source: MSDN Transact-SQL Reference (SQL Server 2000)

Operation of DBCC statements[edit]

DBCC DBREINDEX[edit]

This is a statement is used to recreate the indexes for a particular table![4] This statement rebuilds indexes in a single step.[5] It also assigns fresh pages to reduce internal and external fragmentation.[5]

DBCC DBREPAIR[edit]

This statement is used to drop or delete a damaged database.[6] However, this command is no longer available with Microsoft SQL Server 2005 and later versions of Microsoft SQL Server.[7] Instead, it has been replaced by the DROP DATABASE Transact-SQL statement.[7]

DBCC INDEXDEFRAG[edit]

This statement is used to defragment the clustered and secondary indexes associated with the particular table.[8] The index defragmentation is carried out using the fill factor specified at the time of creation of indexes.[9] While its operation is strikingly similar to that of DBCC DBREINDEX, unlike DBCC INDEXDEFRAG it does not allow new fill factor to be specified.[9]

DBCC SHRINKDATABASE[edit]

This statement is used to reduce the size of a database.[10] This statement reduces the physical size of the database log file.[11][12] An alternate way to shrink a database is to use the commander ALTER DATABASE.[13]

DBCC SHRINKFILE[edit]

This statement is used to reduce the size of a data file or log file of a particular database.[14][15] The file could also be shrunk by using the SHRINKFILE attribute of the ALTER DATABASE command.[13]

DBCC UPDATEUSAGE[edit]

This statement is used to correct inaccuracies in the page and row statistics in the views.[16]

DBCC CLEANTABLE[edit]

This statement is used to remove spaces occupied by columns when they are removed.[17] This feature is not available with Microsoft SQL Server 2000 and has been newly introduced in Microsoft SQL Server 2005[17]

DBCC DROPCLEANBUFFERS[edit]

This statement is used to drop clean buffers from the buffer pool.[18] This feature is available with Microsoft SQL Server 2000 and in Microsoft SQL Server 2005[18]

DBCC FREEPROCCACHE[edit]

This statement is used to remove all elements from the procedure cache.[19] This feature is not available with Microsoft SQL Server 2000 and has been newly introduced in Microsoft SQL Server 2005[19]

DBCC INPUTBUFFER[edit]

This statement is used to display the last statement stored in the buffer.[20]

DBCC OPENTRAN[edit]

This statement is used to display information about the oldest open transaction.[21]

DBCC OUTPUTBUFFER[edit]

This statement is used to return the current value of the output buffer.[22]

DBCC PROCCACHE[edit]

This statement is used to display information about procedure cache.[23]

DBCC SHOWCONTIG[edit]

This statement is used to display fragmentation information[24]

DBCC SHOW_STATISTICS[edit]

This is a statement is used to show current distribution statistics[25]

DBCC SQLPERF[edit]

This statement is used to show transaction log statistics[26]

DBCC TRACESTATUS[edit]

This statement is used to display status of trace flags[27]

DBCC USEROPTIONS[edit]

This statement is used to return set as ACTIVE[28]

DBCC CHECKALLOC[edit]

This statement is used to check whether every extent allocated by the system has been allocated and whether there are extents that have not been allocated.[29]

DBCC CHECKCATALOG[edit]

This statement is used to check for consistency between system tables[30] in the system catalog. It does so through cross-referencing checks.[29]

DBCC CHECKCONSTRAINTS[edit]

This statement is used to check integrity of specific constraints.[31]

DBCC CHECKDB[edit]

This statement is used to check integrity and allocation of specific objects in a database.[32] It also performs DBCC CHECKALLOC, DBCC CHECKTABLE and DBCC CHECKCATALOG in that particular order.[29]

DBCC CHECKFILEGROUP[edit]

This statement is used to check allocation and structural integrity of tables.[33]

DBCC CHECKIDENT[edit]

This statement is used to check identity value of specified table.[34]†

DBCC CHECKTABLE[edit]

This statement is used to check the integrity of a table[35] and all the pages and structures which comprise the table.[29] Both physical and logical checks are performed in this case.[29] However, a PHYSICAL ONLY option can be used to check for physical consistency alone.[29]

DBCC NEWALLOC[edit]

DBCC NEWALLOC is almost similar to DBCC CHECKALLOC. This statement is not supported by recent versions.[36]

DBCC dllname (FREE)[edit]

This statement is used to unload a particular stored procedure DLL from memory.[36]

DBCC HELP[edit]

This statement is used to return syntax information.[37]

DBCC PINTABLE[edit]

This statement is used to mark a particular table to be pinned to SQL memory.[38]

DBCC ROWLOCK[edit]

This statement is used to enable Insert Row Locking (IRL) operations.[39]

DBCC TRACEOFF[edit]

This statement is used to disable a trace flag.[40]

DBCC TRACEON[edit]

This statement is used to turn on a specific trace flag.[41]

DBCC UNPINTABLE[edit]

This statement is used to mark a table as unpinned. In an unpinned table, the table pages in the cache could be easily removed.[42]

Running a Database Console Command[edit]

A database console command could be run from (i) the command window or (ii) query analyzer window.[43]

Advantages of Database Console Commands[edit]

Database Console Commands have a number of advantages. Their use is extremely essential in some instances

  • Occasionally, there have been bad allocations of database pages.[43]
  • Indexes could be destroyed or corrupted easily.[43]
  • There could misunderstandings on the part of the SQL server engine.[43]
  • There could be problems when a large number of updates need to be carried out.[43]
  • Individual pages may lose their optimal storage footprint.[43]

Notes[edit]

  1. ↑ 1.0 1.1 1.2 "DBCC - Transact-SQL Reference (SQL Server 2000)". Microsoft Developer Network (MSDN). Retrieved 2008-06-20.
  2. ↑ Ogle, Pg 285
  3. ↑ Rankins, Ray; Bertucci, Paul; Jensen, Paul (2003). Microsoft SQL Server 2000 unleashed. Sams Publishing. p. 365. ISBN 0672324679, ISBN 978-0-672-32467-3. Search this book on
  4. ↑ "DBCC DBREINDEX, Transact-SQL Reference (SQL Server 2000)". Microsoft Developer Network (MSDN). Retrieved 2008-06-21.
  5. ↑ 5.0 5.1 Dam, Pg 230
  6. ↑ "DBCC DBREPAIR, Transact-SQL Reference (SQL Server 2000)". Microsoft Developer Network (MSDN). Retrieved 2008-06-23.
  7. ↑ 7.0 7.1 "DBCC DBREPAIR, Transact-SQL Reference (SQL Server 2005)". Microsoft Developer Network (MSDN). Retrieved 2008-06-23.
  8. ↑ "DBCC INDEXDEFRAG, Transact-SQL Reference (SQL Server 2000)". Microsoft Developer Network (MSDN). Retrieved 2008-06-23.
  9. ↑ 9.0 9.1 Dam, Pg 236
  10. ↑ Microsoft SQL Server 2000, Pg 142
  11. ↑ Microsoft SQL Server 2000, Pg 398
  12. ↑ Microsoft SQL Server 2000, Pg 402
  13. ↑ 13.0 13.1 Sack, Pg 568
  14. ↑ "DBCC SHRINKFILE, Transact-SQL Reference (SQL Server 2005)". Microsoft Developer Network (MSDN). Retrieved 2008-06-23.
  15. ↑ Microsoft SQL Server 2000, Pg 367 - 370
  16. ↑ "DBCC UPDATEUSAGE, Transact-SQL Reference (SQL Server 2005)". Microsoft Developer Network (MSDN). Retrieved 2008-06-23.
  17. ↑ 17.0 17.1 "DBCC CLEANTABLE, Transact-SQL Reference (SQL Server 2005)". Microsoft Developer Network (MSDN). Retrieved 2008-06-23.
  18. ↑ 18.0 18.1 "DBCC DROPCLEANBUFFERS, Transact-SQL Reference (SQL Server 2005)". Microsoft Developer Network (MSDN). Retrieved 2008-06-23.
  19. ↑ 19.0 19.1 "DBCC FREEPROCCACHE, Transact-SQL Reference (SQL Server 2005)". Microsoft Developer Network (MSDN). Retrieved 2008-06-23.
  20. ↑ "DBCC INPUTBUFFER, Transact-SQL Reference (SQL Server 2005)". Microsoft Developer Network (MSDN). Retrieved 2008-06-23.
  21. ↑ "DBCC OPENTRAN, Transact-SQL Reference (SQL Server 2005)". Microsoft Developer Network (MSDN). Retrieved 2008-06-23.
  22. ↑ "DBCC OUTPUTBUFFER, Transact-SQL Reference (SQL Server 2005)". Microsoft Developer Network (MSDN). Retrieved 2008-06-23.
  23. ↑ "DBCC PROCCACHE, Transact-SQL Reference (SQL Server 2005)". Microsoft Developer Network (MSDN). Retrieved 2008-06-23.
  24. ↑ "DBCC SHOWCONTIG, Transact-SQL Reference (SQL Server 2005)". Microsoft Developer Network (MSDN). Retrieved 2008-06-23.
  25. ↑ "DBCC SHOWSTATISTICS, Transact-SQL Reference (SQL Server 2005)". Microsoft Developer Network (MSDN). Retrieved 2008-06-23.
  26. ↑ "DBCC SQLPERF, Transact-SQL Reference (SQL Server 2005)". Microsoft Developer Network (MSDN). Retrieved 2008-06-23.
  27. ↑ "DBCC TRACESTATUS, Transact-SQL Reference (SQL Server 2005)". Microsoft Developer Network (MSDN). Retrieved 2008-06-23.
  28. ↑ "DBCC USEROPTIONS, Transact-SQL Reference (SQL Server 2005)". Microsoft Developer Network (MSDN). Retrieved 2008-06-23.
  29. ↑ 29.0 29.1 29.2 29.3 29.4 29.5 Petkovic, Pg 403
  30. ↑ "DBCC CHECKCATALOG, Transact-SQL Reference (SQL Server 2005)". Microsoft Developer Network (MSDN). Retrieved 2008-06-23.
  31. ↑ "DBCC CHECKCONSTRAINTS, Transact-SQL Reference (SQL Server 2005)". Microsoft Developer Network (MSDN). Retrieved 2008-06-23.
  32. ↑ "DBCC CHECKDB, Transact-SQL Reference (SQL Server 2005)". Microsoft Developer Network (MSDN). Retrieved 2008-06-23.
  33. ↑ "DBCC CHECKFILEGROUP, Transact-SQL Reference (SQL Server 2005)". Microsoft Developer Network (MSDN). Retrieved 2008-06-23.
  34. ↑ "DBCC CHECKIDENT, Transact-SQL Reference (SQL Server 2005)". Microsoft Developer Network (MSDN). Retrieved 2008-06-23.
  35. ↑ "DBCC CHECKTABLE, Transact-SQL Reference (SQL Server 2005)". Microsoft Developer Network (MSDN). Retrieved 2008-06-23.
  36. ↑ 36.0 36.1 "DBCC NEWALLOC, Transact-SQL Reference (SQL Server 2005)". Microsoft Developer Network (MSDN). Retrieved 2008-06-23.
  37. ↑ "DBCC NEWALLOC, Transact-SQL Reference (SQL Server 2005)". Microsoft Developer Network (MSDN). Retrieved 2008-06-23.
  38. ↑ "DBCC PINTABLE, Transact-SQL Reference (SQL Server 2005)". Microsoft Developer Network (MSDN). Retrieved 2008-06-23.
  39. ↑ "DBCC ROWLOCK, Transact-SQL Reference (SQL Server 2005)". Microsoft Developer Network (MSDN). Retrieved 2008-06-23.
  40. ↑ "DBCC TRACEOFF, Transact-SQL Reference (SQL Server 2005)". Microsoft Developer Network (MSDN). Retrieved 2008-06-23.
  41. ↑ "DBCC TRACEON, Transact-SQL Reference (Suresh)". Microsoft Developer Network (MSDN). Retrieved 2008-06-23.
  42. ↑ "http://msdn.microsoft.com/en-us/library/aa258816(SQL.80).aspx". Microsoft Developer Network (MSDN). Retrieved 2008-06-23. External link in |title= (help)
  43. ↑ 43.0 43.1 43.2 43.3 43.4 43.5 Arthur Fuller (December 21, 2006). "Get out of a jam by using SQL Server's DBCC".

References[edit]

  • Microsoft SQL Server 2000 Database Design and Implementation. Microsoft Press
  • Robert Patton, Travis Laird, Jennifer Ogle (2001). Designing SQL Server 2000 Databases for .NET Enterprise Servers. Syngress. pp. 285–286. ISBN 1928994199, ISBN 978-1-928994-19-0.CS1 maint: Uses authors parameter (link) Search this book on
  • Mike Gunderloy, Joseph L. Jorden, David W. Tschanz (2006). Mastering Microsoft SQL Server 2005. John Wiley and Sons. ISBN 0782143806, ISBN 978-0-7821-4380-5.CS1 maint: Uses authors parameter (link) Search this book on
  • Dušan Petkovic (2008). Microsoft SQL Server 2008: A Beginner's Guide: A Beginner's Guide. McGraw-Hill Professional. ISBN 0071546383, ISBN 978-0-07-154638-6. Search this book on
  • Sajal Dam (2004). SQL Server Query Performance Tuning Distilled. Apress. ISBN 1590594215, ISBN 978-1-59059-421-6. Search this book on


This article "Database Console Commands (Transact-SQL)" is from Wikipedia. The list of its authors can be seen in its historical. Articles copied from Draft Namespace on Wikipedia could be seen on the Draft Namespace of Wikipedia and not main one.


Retrieved from "https://en.everybodywiki.com/index.php?title=Database_Console_Commands_(Transact-SQL)&oldid=2264862"
License CC BY-SA 3.0
Powered by MediaWiki
Powered by Semantic MediaWiki