{"id":1098,"date":"2018-02-02T10:09:40","date_gmt":"2018-02-02T09:09:40","guid":{"rendered":"https:\/\/dbafox.com\/?p=1098"},"modified":"2018-02-02T10:11:09","modified_gmt":"2018-02-02T09:11:09","slug":"sql-server-dbcc","status":"publish","type":"post","link":"https:\/\/dbafox.com\/?p=1098","title":{"rendered":"SQL Server &#8211; DBCC"},"content":{"rendered":"\n<pre class=\"toolbar-overlay:false lang:tsql decode:true \" title=\"DBCC\" >DBCC TRACEON(2520)\r\nDBCC HELP (\u2018?\u2019)\r\nGO\r\n\r\nDBCC HELP(&lt;command&gt;)\r\nGO\r\nFollowing is the list of all the DBCC commands and their syntax. List contains all documented and undocumented DBCC commands.\r\nDBCC activecursors [(spid)]\r\n\r\nDBCC addextendedproc (function_name, dll_name)\r\n\r\nDBCC addinstance (objectname, instancename)\r\n\r\nDBCC adduserobject (name)\r\n\r\nDBCC auditevent (eventclass, eventsubclass, success, loginname\r\n, rolename, dbusername, loginid)\r\n\r\nDBCC autopilot (typeid, dbid, tabid, indid, pages [,flag])\r\n\r\nDBCC balancefactor (variance_percent)\r\n\r\nDBCC bufcount [(number_of_buffers)]\r\n\r\nDBCC buffer ( {\u2018dbname\u2019 | dbid} [, objid [, number [, printopt={0|1|2} ] [, dirty | io | kept | rlock | ioerr | hashed ]]])\r\n\r\nDBCC bytes ( startaddress, length )\r\n\r\nDBCC cachestats\r\n\r\nDBCC callfulltext\r\n\r\nDBCC checkalloc [(\u2018database_name'[, NOINDEX | REPAIR])] [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, ESTIMATEONLY]]\r\n\r\nDBCC checkcatalog [(\u2018database_name\u2019)] [WITH NO_INFOMSGS]\r\n\r\nDBCC checkconstraints [( \u2018tab_name\u2019 | tab_id | \u2018constraint_name\u2019 | constraint_id )] [WITH ALL_CONSTRAINTS | ALL_ERRORMSGS]\r\n\r\nDBCC checkdb [(\u2018database_name'[, NOINDEX | REPAIR])] [WITH NO_INFOMSGS[, ALL_ERRORMSGS] [, PHYSICAL_ONLY][, ESTIMATEONLY][,DBCC TABLOCK]\r\n\r\nDBCC checkdbts (dbid, newTimestamp)]\r\n\r\nDBCC checkfilegroup [( [ {\u2018filegroup_name\u2019 | filegroup_id} ] [, NOINDEX] )] [WITH NO_INFOMSGS\r\n[, ALL_ERRORMSGS][, PHYSICAL_ONLY][, ESTIMATEONLY][, TABLOCK]]\r\n\r\nDBCC checkident (\u2018table_name'[, { NORESEED | {RESEED [, new_reseed_value] } } ] )\r\n\r\nDBCC checkprimaryfile ( {\u2018FileName\u2019} [, opt={0|1|2|3} ])\r\n\r\nDBCC checktable (\u2018table_name'[, {NOINDEX | index_id | REPAIR}])\r\n[WITH NO_INFOMSGS[, ALL_ERRORMSGS] [, PHYSICAL_ONLY][, ESTIMATEONLY][, TABLOCK]]\r\n\r\nDBCC cleantable (\u2018database_name\u2019|database_id, \u2018table_name\u2019|table_id,[batch_size])\r\n\r\nDBCC cacheprofile [( {actionid} [, bucketid])\r\n\r\nDBCC clearspacecaches (\u2018database_name\u2019|database_id,\r\n\u2018table_name\u2019|table_id, \u2018index_name\u2019|index_id)\r\n\r\nDBCC collectstats (on | off)\r\n\r\nDBCC concurrencyviolation (reset | display | startlog | stoplog)\r\n\r\nDBCC config\r\n\r\nDBCC cursorstats ([spid [,\u2019clear\u2019]])\r\n\r\nDBCC dbinfo [(\u2018dbname\u2019)]\r\n\r\nDBCC dbrecover (dbname [, IgnoreErrors])\r\n\r\nDBCC dbreindex (\u2018table_name\u2019 [, index_name [, fillfactor ]]) [WITH NO_INFOMSGS]\r\n\r\nDBCC dbreindexall (db_name\/db_id, type_bitmap)\r\n\r\nDBCC dbrepair (\u2018dbname\u2019, DROPDB [, NOINIT])\r\n\r\nDBCC dbtable [({\u2018dbname\u2019 | dbid})]\r\n\r\nDBCC debugbreak\r\n\r\nDBCC deleteinstance (objectname, instancename)\r\n\r\nDBCC des [( {\u2018dbname\u2019 | dbid} [, {\u2018objname\u2019 | objid} ])]\r\n\r\nDBCC detachdb [( \u2018dbname\u2019 )]\r\n\r\nDBCC dropcleanbuffers\r\n\r\nDBCC dropextendedproc (function_name)\r\n\r\nDBCC dropuserobject (\u2018object_name\u2019)\r\n\r\nDBCC dumptrigger ({\u2018BREAK\u2019, {0 | 1}} | \u2018DISPLAY\u2019 | {\u2018SET\u2019, exception_number}\r\n| {\u2018CLEAR\u2019, exception_number})\r\n\r\nDBCC errorlog\r\n\r\nDBCC extentinfo [({\u2018database_name\u2019| dbid | 0}\r\n[,{\u2018table_name\u2019 | table_id} [, {\u2018index_name\u2019 | index_id | -1}]])]\r\n\r\nDBCC fileheader [( {\u2018dbname\u2019 | dbid} [, fileid])\r\n\r\nDBCC fixallocation [({\u2018ADD\u2019 | \u2018REMOVE\u2019},\r\n{\u2018PAGE\u2019 | \u2018SINGLEPAGE\u2019 | \u2018EXTENT\u2019 | \u2018MIXEDEXTENT\u2019}\r\n, filenum, pagenum [, objectid, indid])\r\n\r\nDBCC flush (\u2018data\u2019 | \u2018log\u2019, dbid)\r\n\r\nDBCC flushprocindb (database)\r\n\r\nDBCC free dll_name (FREE)\r\n\r\nDBCC freeproccache\r\n\r\ndbcc freeze_io (db)\r\n\r\ndbcc getvalue (name)\r\n\r\ndbcc icecapquery (\u2018dbname\u2019, stored_proc_name\r\n[, #_times_to_icecap (-1 infinite, 0 turns off)])\r\nUse \u2018dbcc icecapquery (printlist)\u2019 to see list of SP\u2019s to profile.\r\nUse \u2018dbcc icecapquery (icecapall)\u2019 to profile all SP\u2019s.\r\n\r\ndbcc incrementinstance (objectname, countername, instancename, value)\r\n\r\ndbcc ind ( { \u2018dbname\u2019 | dbid }, { \u2018objname\u2019 | objid }, { indid | 0 | -1 | -2 } )\r\n\r\nDBCC indexdefrag ({dbid | dbname | 0}, {tableid | tablename}, {indid |indname})\r\n\r\nDBCC inputbuffer (spid)\r\n\r\nDBCC invalidate_textptr (textptr)\r\n\r\nDBCC invalidate_textptr_objid (objid)\r\n\r\nDBCC iotrace ( { \u2018dbname\u2019 | dbid | 0 | -1 }\r\n, { fileid | 0 }, bufsize, [ { numIOs | -1 }\r\n[, { timeout (sec) | -1 } [, printopt={ 0 | 1 }]]] )\r\n\r\nDBCC latch ( address [, \u2018owners\u2019] [, \u2018stackdumps\u2019])\r\n\r\nDBCC lock ([{\u2018DUMPTABLE\u2019 | \u2018DUMPSTATS\u2019 | \u2018RESETSTATS\u2019 | \u2018HASH\u2019}] |\r\n[{\u2018STALLREPORTTHESHOLD\u2019, stallthreshold}])\r\n\r\nDBCC lockobjectschema (\u2018object_name\u2019)\r\n\r\nDBCC log ([dbid[,{0|1|2|3|4}[,[\u2018lsn\u2019,'[0x]x:y:z\u2019]|[\u2018numrecs\u2019,num]|[\u2018xdesid\u2019,\u2019x:y\u2019] |[\u2018extent\u2019,\u2019x:y\u2019]|[\u2018pageid\u2019,\u2019x:y\u2019]|[\u2018objid\u2019,{x,\u2019y\u2019}]|[\u2018logrecs\u2019,\r\n{\u2018lop\u2019|op}\u2026]|[\u2018output\u2019,x,[\u2018filename\u2019,\u2019x\u2019]]\u2026]]])\r\n\r\nDBCC loginfo [({\u2018database_name\u2019 | dbid})]\r\n\r\nDBCC matview ({\u2018PERSIST\u2019 | \u2018ENDPERSIST\u2019 | \u2018FREE\u2019 | \u2018USE\u2019 | \u2018ENDUSE\u2019})\r\n\r\nDBCC memobjlist [(memory object)]\r\n\r\nDBCC memorymap\r\n\r\nDBCC memorystatus\r\n\r\nDBCC memospy\r\n\r\nDBCC memusage ([IDS | NAMES], [Number of rows to output])\r\n\r\nDBCC monitorevents (\u2018sink\u2019 [, \u2018filter-expression\u2019])\r\n\r\nDBCC newalloc \u2013 please use checkalloc instead\r\n\r\nDBCC no_textptr (table_id , max_inline)\r\n\r\nDBCC opentran [({\u2018dbname\u2019| dbid})] [WITH TABLERESULTS[,NO_INFOMSGS]]\r\n\r\nDBCC outputbuffer (spid)\r\n\r\nDBCC page ( {\u2018dbname\u2019 | dbid}, filenum, pagenum\r\n[, printopt={0|1|2|3} ][, cache={0|1} ])\r\n\r\nDBCC perflog\r\n\r\nDBCC perfmon\r\n\r\nDBCC pglinkage (dbid, startfile, startpg, number, printopt={0|1|2}\r\n, targetfile, targetpg, order={1|0})\r\n\r\nDBCC pintable (database_id, table_id)\r\n\r\nDBCC procbuf [({\u2018dbname\u2019 | dbid}[, {\u2018objname\u2019 | objid}\r\n[, nbufs[, printopt = { 0 | 1 } ]]] )]\r\n\r\nDBCC proccache\r\n\r\nDBCC prtipage (dbid, objid, indexid [, [{{level, 0}\r\n| {filenum, pagenum}}] [,printopt]])\r\n\r\nDBCC pss [(uid[, spid[, printopt = { 1 | 0 }]] )]\r\n\r\nDBCC readpage ({ dbid, \u2018dbname\u2019 }, fileid, pageid\r\n, formatstr [, printopt = { 0 | 1} ])\r\n\r\nDBCC rebuild_log (dbname [, filename])\r\n\r\nDBCC renamecolumn (object_name, old_name, new_name)\r\n\r\nDBCC resource\r\n\r\nDBCC row_lock (dbid, tableid, set) \u2013 Not Needed\r\n\r\nDBCC ruleoff ({ rulenum | rulestring } [, { rulenum | rulestring } ]+)\r\n\r\nDBCC ruleon ( rulenum | rulestring } [, { rulenum | rulestring } ]+)\r\n\r\nDBCC setcpuweight (weight)\r\n\r\nDBCC setinstance (objectname, countername, instancename, value)\r\n\r\nDBCC setioweight (weight)\r\n\r\nDBCC show_statistics (\u2018table_name\u2019, \u2018target_name\u2019)\r\n\r\nDBCC showcontig (table_id | table_name [, index_id | index_name] [WITH FAST, ALL_INDEXES, TABLERESULTS [,ALL_LEVELS]])\r\n\r\nDBCC showdbaffinity\r\n\r\nDBCC showfilestats [(file_num)]\r\n\r\nDBCC showoffrules\r\n\r\nDBCC showonrules\r\n\r\nDBCC showtableaffinity (table)\r\n\r\nDBCC showtext (\u2018dbname\u2019, {textpointer | {fileid, pageid, slotid[,option]}})\r\n\r\nDBCC showweights\r\n\r\nDBCC shrinkdatabase ({dbid | \u2018dbname\u2019}, [freespace_percentage\r\n[, {NOTRUNCATE | TRUNCATEONLY}]])\r\n\r\nDBCC shrinkfile ({fileid | \u2018filename\u2019}, [compress_size\r\n[, {NOTRUNCATE | TRUNCATEONLY | EMPTYFILE}]])\r\n\r\nDBCC sqlmgrstats\r\n\r\nDBCC sqlperf (LOGSPACE)({IOSTATS | LRUSTATS | NETSTATS | RASTATS [, CLEAR]}\r\n| {THREADS} | {LOGSPACE})\r\n\r\nDBCC stackdump [( {uid[, spid[, ecid]} | {threadId, \u2018THREADID\u2019}] )]\r\n\r\nDBCC tab ( dbid, objid )\r\n\r\nDBCC tape_control {\u2018query\u2019 | \u2018release\u2019}[,(\u2018.tape\u2019)]\r\n\r\nDBCC tec [( uid[, spid[, ecid]] )]\r\n\r\nDBCC textall [({\u2018database_name\u2019|database_id}[, \u2018FULL\u2019 | FAST] )]\r\n\r\nDBCC textalloc ({\u2018table_name\u2019|table_id}[, \u2018FULL\u2019 | FAST])\r\n\r\nDBCC thaw_io (db)\r\n\r\nDBCC traceoff [( tracenum [, tracenum \u2026 ] )]\r\n\r\nDBCC traceon [( tracenum [, tracenum \u2026 ] )]\r\n\r\nDBCC tracestatus (trace# [, \u2026trace#])\r\n\r\nDBCC unpintable (dbid, table_id)\r\n\r\nDBCC updateusage ({\u2018database_name\u2019| 0} [, \u2018table_name\u2019 [, index_id]])\r\n[WITH [NO_INFOMSGS] [,] COUNT_ROWS]\r\n\r\nDBCC upgradedb (db) DBCC usagegovernor (command, value)\r\n\r\nDBCC useplan [(number_of_plan)]\r\n\r\nDBCC useroptions DBCC wakeup (spid)\r\n\r\nDBCC writepage ({ dbid, \u2018dbname\u2019 }, fileid, pageid, offset, length, data)\r\n\r\nReference : Pinal Dave (https:\/\/blog.sqlauthority.com) , BOL, Extreme Expert\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>DBCC TRACEON(2520) DBCC HELP (\u2018?\u2019) GO DBCC HELP(&lt;command&gt;) GO Following is the list of all the DBCC commands and their syntax. List contains all documented and undocumented DBCC commands. DBCC activecursors [(spid)] DBCC addextendedproc (function_name, dll_name) DBCC addinstance (objectname, instancename) &hellip; <a href=\"https:\/\/dbafox.com\/?p=1098\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"pgc_sgb_lightbox_settings":"","_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1098","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.6 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>SQL Server - DBCC - dbafox<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbafox.com\/?p=1098\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server - DBCC - dbafox\" \/>\n<meta property=\"og:description\" content=\"DBCC TRACEON(2520) DBCC HELP (\u2018?\u2019) GO DBCC HELP(&lt;command&gt;) GO Following is the list of all the DBCC commands and their syntax. List contains all documented and undocumented DBCC commands. DBCC activecursors [(spid)] DBCC addextendedproc (function_name, dll_name) DBCC addinstance (objectname, instancename) &hellip; Continue reading &rarr;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbafox.com\/?p=1098\" \/>\n<meta property=\"og:site_name\" content=\"dbafox\" \/>\n<meta property=\"article:published_time\" content=\"2018-02-02T09:09:40+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-02-02T09:11:09+00:00\" \/>\n<meta name=\"author\" content=\"Ray Fox\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Ray Fox\" \/>\n\t<meta name=\"twitter:label2\" content=\"Estimated reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbafox.com\/?p=1098\",\"url\":\"https:\/\/dbafox.com\/?p=1098\",\"name\":\"SQL Server - DBCC - dbafox\",\"isPartOf\":{\"@id\":\"https:\/\/dbafox.com\/#website\"},\"datePublished\":\"2018-02-02T09:09:40+00:00\",\"dateModified\":\"2018-02-02T09:11:09+00:00\",\"author\":{\"@id\":\"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb\"},\"breadcrumb\":{\"@id\":\"https:\/\/dbafox.com\/?p=1098#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbafox.com\/?p=1098\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbafox.com\/?p=1098#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbafox.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server &#8211; DBCC\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbafox.com\/#website\",\"url\":\"https:\/\/dbafox.com\/\",\"name\":\"dbafox\",\"description\":\"DBA Technical Information\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbafox.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-GB\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb\",\"name\":\"Ray Fox\",\"sameAs\":[\"http:\/\/www.dbafox.com\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server - DBCC - dbafox","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dbafox.com\/?p=1098","og_locale":"en_GB","og_type":"article","og_title":"SQL Server - DBCC - dbafox","og_description":"DBCC TRACEON(2520) DBCC HELP (\u2018?\u2019) GO DBCC HELP(&lt;command&gt;) GO Following is the list of all the DBCC commands and their syntax. List contains all documented and undocumented DBCC commands. DBCC activecursors [(spid)] DBCC addextendedproc (function_name, dll_name) DBCC addinstance (objectname, instancename) &hellip; Continue reading &rarr;","og_url":"https:\/\/dbafox.com\/?p=1098","og_site_name":"dbafox","article_published_time":"2018-02-02T09:09:40+00:00","article_modified_time":"2018-02-02T09:11:09+00:00","author":"Ray Fox","twitter_misc":{"Written by":"Ray Fox","Estimated reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/dbafox.com\/?p=1098","url":"https:\/\/dbafox.com\/?p=1098","name":"SQL Server - DBCC - dbafox","isPartOf":{"@id":"https:\/\/dbafox.com\/#website"},"datePublished":"2018-02-02T09:09:40+00:00","dateModified":"2018-02-02T09:11:09+00:00","author":{"@id":"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb"},"breadcrumb":{"@id":"https:\/\/dbafox.com\/?p=1098#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbafox.com\/?p=1098"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/dbafox.com\/?p=1098#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbafox.com\/"},{"@type":"ListItem","position":2,"name":"SQL Server &#8211; DBCC"}]},{"@type":"WebSite","@id":"https:\/\/dbafox.com\/#website","url":"https:\/\/dbafox.com\/","name":"dbafox","description":"DBA Technical Information","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbafox.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-GB"},{"@type":"Person","@id":"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb","name":"Ray Fox","sameAs":["http:\/\/www.dbafox.com"]}]}},"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p3ecMb-hI","_links":{"self":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts\/1098","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1098"}],"version-history":[{"count":4,"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts\/1098\/revisions"}],"predecessor-version":[{"id":1101,"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts\/1098\/revisions\/1101"}],"wp:attachment":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1098"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1098"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1098"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}