sql server未经确认的dbcc命令

最新推荐文章于 2022-08-08 14:50:00 发布

yethyeth

最新推荐文章于 2022-08-08 14:50:00 发布

阅读量2.2k

点赞数

分类专栏： SQL SERVER 文章标签： sql server parameters generation descriptor table delete

SQL SERVER 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

（2000和7中支持的dbcc命令）http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocDBCC.htm

http://www.windowsitlibrary.com/Content/77/21/1.html

下面这些dbcc command有些是没有官方支持的，不建议初学者使用。切记小心使用，不过有很多还是非常有用，有兴趣的可以测试看看！（这些命令似乎在sql server6.5中可用）

Unauthorized
of Sybase DBCC Commands

Use them at your own risk!

dbcc allocdump ( dbid, page )
Displays all extents on an allocation page.
      Parameters
      dbid database ID
      page allocation page number

dbcc allocmap ( dbid, one_all, allocpg, bit_stat )
Display or modify status of allocation bitmaps used to detect full allocation
pages.
      Parameters
      ParamDescriptionValid Values
      dbid database ID
      one_all bitmap(s) to check/modify 0 entire allocation map
      1 single allocation map
      allocpg allocation page, if one_all = 1
      bit_stat status of the bits 0 check the status
      1 set the bit(s)
      2 clear the bit(s)

dbcc bhash ( print, bucket_limit )
Check integrity of and optionally print the buffer hash table.
      Parameters
      ParamDescriptionValid Values
      print print option print_bufs display all buffers (default)
      no_print display only buffers with problems
      bucket_limit number of buffers allowed in a bucket. default = 0

dbcc bufcount ( n_chains )
Print up to 10 longest buffer chains and average chain length.
      Parameters
      ParamDescriptionValid Values
      n_chains the number of chains to print 1-10

dbcc buffer ( [ dbid ][, objid ][, nbufs ], printopt, buftype )
Prints buffer headers and pages from the buffer cache.
      Parameters
      ParamDescriptionValid Values
      dbid (optional) database name or database ID 0 will show data for all
      databases.
      objid (optional) object ID 0 will show data for all objects.
      nbufs (optional) number of buffers to examine. If positive, the nbufs
      buffers which meet the other criteria (dbid, objid) are printed in MRU to
      LRU order. If negative, the last nbufs buffers which meet the criteria are
      printed in LRU to MRU order. If 0, all buffers will be printed in MRU to
      LRU order.
      printopt print option 0 print out only the buffer header and page header
      (default)
      1 print out each row separately and the offset table
      2 print out each row as a whole and the offset table
      buftype buffer type to print Can be numeric or a symbolic character
      string. See table below for possible strings and their hexadecimal
      equivalents

      Buffer Types
      ParamHexDescription
      BUF_DIRTY 1 Buffer in cache differs from disk image.
      BUF_IO 2 Buffer is being read or written.
      BUF_PINNED 4 The log must be written before this buffer is.
      BUF_KEPT 8 Some process is using this buffer.
      BUF_RLOCK 10 The buffer is resource locked.
      BUF_TRIP 20 The buffer ages out of cache more slowly.
      BUF_NEW 40 Buffer contains a new page and has never been written.
      BUF_RWAIT 80 The process is waiting for rlock.
      BUF_IOERR 100 There was an error writing tis buffer.
      BUF_SPECIAL 200 If this buffer is written, update relevant descriptors.
      BUF_WQUEUE 400 This buffer is queued for an out-of-sequence database dump.

      BUF_NOTHASHED 800 This buffer is not hashed.
      BUF_HASHED 1000 This buffer is hashed.
      BUF_DIAG 2000 This is a special buffer used for diagnostics only.
      BUF_FILTHY 4000 The buffer was dirtied while in I/O (log only).
      BUF_NORELEASE 8000 This is used for coordinating releases of rlocks on
      allocation pages.

dbcc bytes ( startaddress, length )
Dump out bytes from specific address.
      Parameters
      ParamDescription
      startaddress starting address to dump
      length number of bytes to dump

dbcc cacheremove ( dbname, objectname )
Removes object from cache.
      Parameters
      ParamDescription
      dbname database name
      objname object name

dbcc capture_tds (spid)
Captures Tabular Data Stream into a file called $SYBASE/install/TDSDUMP. File
contains full stream, including passwords -- chmod the file to protect this
information.

dbcc checkalloc [( dbname [, option ] )]
Check data and index pages against corresponding extent structures (earlier
newalloc).
      Parameters
      ParamDescriptionValid Values
      dbname (optional) database name
      option (optional) fix option fix report and fix descrepancies
      nofix report decrepancies only (default)

dbcc checkcatalog [( dbname)]
Check consistency of system catalogs tables for database named.
      Parameters
      ParamDescription
      dbname (optional) database name; default is current db

dbcc checkdb [( dbname [, skip_ncindex ] )]
Check consistency of data and index pages.
      Parameters
      ParamDescription
      dbname (optional) database name; default is current db
      skip_ncindex (optional) skip check on non clustered indexes for the
      database

dbcc checktable ( tabid [, skip_ncindex ] )
Check consistency of data and index pages.
      Parameters
      ParamDescription
      tabid table name or table ID
      skip_ncindex (optional) skip check on non clustered indexes for the table.

dbcc chgdbname ( dbname, newname )
Changes database name in DBINFO only.
      Parameters
      ParamDescription
      dbname existing database name
      newname new database name

dbcc chgobjname ( objname, newname )
Changes object name in cache only.
      Parameters
      ParamDescription
      objname existing object name
      newname new object name

dbcc cursorinfo ( cursor_level, cursor_name )
Displays cursor information.
      Parameters
      ParamDescription
      cursor_level level number; -1 to get cursor info for all nesting levels
      cursor_name name of the cursor

dbcc dbinfo [( dbname )]
Print DBINFO structure for specified database.
      Parameters
      ParamDescription
      dbname (optional) database name

dbcc dbrecover ( dbname )
This will have recovery run on a suspect database without rebooting the server.
      Parameters
      ParamDescription
      dbname database name

dbcc dbrepair ( db, option [, table][, index] )
Repair system tables.
      Parameters
      ParamDescriptionValid Values
      db database ID or database name
      option repair option dropdb drop the specified database
      findstrandeddisplay stranded objects
      fixindexrebuild the specified index; used on system catalogs only
      fixsysindexcorrect pfreeoff value for sysindexes
      newthresholdrebuild the database threshold table
      remapupdate the in-core image of the database usage map table
      repairindexrepair index for user tables
      table (optional) table name
      index (optional) index ID

dbcc dbtable ( dbid )
Print out the contents of the DBTABLE structure.
      Parameters
      ParamDescription
      dbid database ID

dbcc delbuff ( dbid, page, dirty_flush, kept_flag, rlock_flag )
Remove buffer from cache, optionally flush to disk.
      Parameters
      ParamDescription
      dbid database ID
      page the logical page number of the page to be removed
      dirty_flush if 1, then flush the page to disk if DIRTY
      kept_flag if 1, then do not unkeep buffer if KEPT
      rlock_flag if 1, then do not remove RESOURCE LOCK

dbcc delete_row ( dbid, page, delete_by_row, rownum )
Delete an index or data row by either a row number or an offset on a page.
      Parameters
      ParamDescriptionValid Values
      dbid database ID
      page the logical page number
      delete_by_row delete location; by row or by offset 1 next parameter is
      interpreted as row number
      0 next parameter is interpreted as offset on the page
      rownum either a row number or offset

dbcc des [( [ dbid ][, objid ] )]
Prints the contents of the specified DES (descriptor).
      Parameters
      ParamDescription
      dbid (optional) database ID
      objid (optional) object ID

dbcc descount ( n_chains )
Print up to 10 longest DES chains. P>Parameters
      ParamDescription
      n_chains the number of chains to print, up to 10.

dbcc desdiag ( dbid, option )
Produce DES diagnostic reports.
      Parameters
      ParamDescriptionValid Values
      dbid database ID
      optiondiagnostic to run -4 Display Hash Table Graph
      -3 Display Diagnostic Maps
      -2 Run all the tests (1-4)
      -1 Return to caller - Exit
      0 Redisplay this menu
      1 Check Free Des Chain
      2 Check Dbtable DES chain
      3 Check Des Kept/Unkept chain
      4 Check Des Name/Id hash table
      5 Display 'lost' descriptors
      6 Run tests (1-4), kepanic on errors

dbcc engine (engine, func)
      Parameters
      ParamDescription
      engine engine number
      func "online"
      "offline"

dbcc extentchain ( dbid, objid, indexid, sort, display, [, order ] )
Displays or counts extents following the chain, checking chain integrity.
      Parameters
      ParamDescriptionValid Values
      dbid database ID
      objid object ID
      indexid index ID
      sort report on state of the sort bit 0 don't include this info
      1 do report on sort bit status
      display what to report 0 display the count only
      1 display the extents
      order (optional) order to follow the chain 0 descending
      1 ascending

dbcc extentcheck ( dbid, objid, indexid, sort )
Examine all extents on allocation pages for specified object.
      Parameters
      ParamDescriptionValid Values
      dbid database ID
      objid object ID
      indexid index ID
      state of the sort bit0 don't report this
      1 report sort bit status

dbcc extentdump ( dbid, page )
Display an extent.
      Parameters
      ParamDescription
      dbid database ID
      page the number of a page controlled by the extent in question

dbcc extentzap ( dbid, objid, indexid, sort )
Clear all extents matching the parameter values.
      Parameters
      ParamDescriptionValid Values
      dbid database ID
      objid object ID
      indexid index ID
      sortstate of the sort bit 0
      1

dbcc findnotfullextents ( dbid, objid, indexid, sort )
Find all not full extents on all allocation pages for the specified object.
      Parameters
      ParamDescriptionValid Values
      dbid database ID
      objid object ID
      indexid index ID
      sortstate of the sort bit 0
      1

dbcc fix_al [( dbname )]
Make allocation bitmaps match the page chains.
      Parameters
      ParamDescription
      dbname (optional) database name

dbcc fix_text ( table )
To upgrade text values after converting the server to a multi-byte character
set.
      Parameters
      ParamDescription
      table table name or table ID

dbcc gettrunc ()
Replication Server related: Prints current LTM (Log Transfer Manager)
information for the database.

dbcc help ( dbcc_command )
Give syntax on specific dbcc command.
      Parameters
      ParamDescription
      dbcc_command the command for which syntax help is desired

dbcc ind ( dbid, objid, printopt )
Print each index page for a specified table.
      Parameters
      ParamDescriptionValid Values
      dbid database ID
      objid object ID
      printopt print option 0 print out only the buffer header and page header
      (default)
      1 print page headers, page data in row format, and offset tables
      2 print page headers, unformatted page data, and offset tables

dbcc indexalloc ( table, indid [, report][, fix] )
      Parameters
      ParamDescriptionValid Values
      table table name or table ID
      indid index ID
      report (optional) report style full complete details
      optimized essential details (default)
      fast quick report
      fix (optional) fix flag fix fix allocation errors on traversal (default)
      nofix do not fix allocation errors

dbcc is_replication_enabled
Returns True/False via @@error value. Immediately after running this command,
select @@error. If @@error = 0, replication is enabled, else replication is not
enabled.

dbcc listoam ( db, objid, indid )
Lists object allocation information.
      Parameters
      ParamDescription
      db database ID or database name
      objid object ID
      indid (optional) index ID

dbcc locateindexpgs ( dbid, objid, page, indexid, level )
Print all references in the index to the specified page.
      Parameters
      ParamDescription
      dbid database ID
      objid object ID
      page logical page number of the page for which index references are being
      searched
      indexid index ID
      level level within the index to search for references

dbcc lock
Print out lock chains.

dbcc lockunit ( file_name )
      Parameters
      ParamDescription
      file_name file in $SYBASE/install

dbcc log ( [dbid][, objid][, pagenum][, rownum][, nrecords][, type], printopt )
Print log records.
      Parameters
      ParamDescriptionValid Values
      dbid (optional) database ID
      objid (optional) object ID
      pagenum (optional) page number ORif given with no rownum
      transaction ID if rownum is supplied
      rownum (optional) transaction ID if given with pagenum
      nrecords (optional) number of records to examine If positive, the first
      nrecords log entries which meet the criteria (dbid, objid, pagenum,
      rownum) are printed in forward order.
      If negative, the last nrecords which meet the criteria are printed in
      reverse order.
      type (optional) type of record to print -1 Print all records
      0 XREC_BEGINXACT
      1 XREC_CHSYSIND
      2 XREC_SWITCHTO
      3 XREC_RETURN
      4 XREC_INSERT
      5XREC_DELETE
      6 XREC_INSIND
      7XREC_IINSERT
      8 XREC_IDELETE
      9 XREC_MODIFY
      10XREC_NOOP
      11 XREC_INOOP
      12 XREC_DNOOP
      13 XREC_ALLOC
      14 XREC_DBNEXTID
      15XREC_EXTENT Allocate extents without pages allocated.
      16 XREC_SPLIT
      17 XREC_CHECKPOINT
      18 XREC_SAVEXACT
      19 XREC_CMD
      20 XREC_DEXTENT Deallocate extents.
      21 XREC_DEALLOC Deallocate page.
      22 XREC_DROPEXTS Delete all extents on an allocation page.
      23 XREC_AEXTENTS Allocate extent and mark all pages used.
      24 XREC_SALLOC Allocate for new page of a split.
      25 XREC_PGCHGSYSIND Direct change to sysindexes.
      27 XREC_SORT
      28 XREC_SODEALLOC
      29 XREC_ALTDB
      30 XREC_ENDXACT
      31 XREC_SORTTS
      32 XREC_TEXT Direct inserts.
      33 XREC_INOOPTEXT Deferred TEXT insert.
      34 XREC_DNOOPTEXT Deferred TEXT delete.
      35 XREC_ISINDTEXT Indirect insert.
      36 XREC_TEXTDELETE Delete text
      printopt print option 0 print out header information only (default)
      1 print out header and data information.

dbcc logtransfer ('reserve', 'context')
dbcc logtransfer ('scan', {'normal'|'repeat'|'continue'})
Refer to your Rep Server manual.

dbcc memusage
Display detailed information on server's memory usage.

dbcc monitor ( task, group, option )
Allows a more detailed monitoring of the SQL Server. [Hint: Run dbcc
traceon(8399) first, to enable descriptive names for groups.]
      Parameters
      ParamValuePurpose
      task"clear" clear earlier gathered statistics
      "sample" gather fresh statistics
      "select" enable statistics to be displayed
      group "all" all groups' statistics are gathered/displayed
      group_name name of the group for which statistics are to be
      gathered/displayed
      option "on" enable monitoring
      "off" discontinue monitoring

dbcc netmemshow ([ option ])
Prints network memory allocations.
      Parameters
      ParamValuePurpose
      option (optional) 1 summary information
      2 or 3 all information (default)

dbcc netmemusage
Prints memory usage details.

dbcc newalloc ( dbname, option )
Reports on pages and extents in use.
      Parameters
      ParamDescription
      dbname database name
      option 1, 2, or 3


Caveat Emptor!!
Use them at your own risk!

dbcc page ( dbid, pagenum [, printopt ][, cache ][, logical ] )
Prints the contents of the specified page.
      Parameters
      ParamDescriptionValid Values
      dbid database ID
      pagenum the logical or virtual page number
      printopt (optional) print option 0 print out only the buffer header and
      page header (default)
      1 print buffer headers, page header, each row separately and the offset
      table
      2 print out buffer and page headers, page as a whole and the offset table.

      cache (optional) handling of pages in cache 0 print out the page as found
      on disk
      1 print out the page as found in cache if it resides in cache, otherwise
      retrieve the page from disk and print (default).
      logical (optional) type of page 0 pagenum is taken to a virtual page
      number
      1 pagenum is taken to be a logical page number (default)

dbcc pglinkage ( dbid, start, number, printopt, target, order )
Display the page chain, performing integrity checks during traversal.
      Parameters
      ParamDescriptionValid Values
      dbid database ID
      start page number with which to start
      number number of pages to examine, or 0 if target is specified
      printopt print option 0 display only the count of pages scanned
      1 display information about the last 16 pages scanned
      2 display all page numbers in the scan
      target the particular page we are looking for, or 0 if number is specified
      Specifying a target will continue to search in the specified direction
      until the target page or the end of the chain is found.
      ordertraversal order0 descending
      1 ascending

dbcc pktmemshow ( [spid] )
Lists network buffers for active processes.
      Parameters
      ParamDescription
      spid (optional) server process ID

dbcc procbuf ( [ dbid] [, objid] [, nbufs] [, printopt ] )
Print procedure buffer headers and proc-headers from the procedure cache.
      Parameters
      ParamDescriptionValid Values
      dbid (optional) database ID
      objid (optional) object ID
      nbufs (optional) number of buffers to print
      printopt (optional) print option 0 print out only the proc buff and proc
      header (default)
      1 print out proc buff, proc header and contents of buffer.

dbcc procdiag ( dbid, option )
Produce diagnostics on procedure buffers
      Parameters
      ParamDescriptionValid Values
      dbid database ID
      optiondiagnostic to run 0 display option menu
      1 display diag bitmaps
      2 display procbufs in cache
      3 run summary diagnostics on entire cache

dbcc prtipage ( dbid, objid, indexid, indexpage )
Print the page number pointed to by each row on the specified index page.
      Parameters
      ParamDescription
      dbid database ID
      objid object ID
      indexid index ID
      indexpage the logical page number of the index page to dump

dbcc pss ( suid, spid, printopt )
Prints the contents of the specified PSS structure.
      Parameters
      ParamDescriptionValid Values
      suid (optional) server user ID
      spid (optional) server process ID
      printopt print option 0 standard output
      1 all open DES's and current sequence tree
      2 print all locks held by the process to the errorlog.

dbcc rebuild_log ( dbname, rebuild, all )
Generates a new log containing a single checkpoint record.
      Parameters
      ParamDescriptionValid Values
      dbname database name
      rebuild rebuild log flag 0 do not create a new log
      1 create a new log
      all remove log flag 0 old preserved log removed
      1 old preserved and new log removed

dbcc rebuildextents ( dbid, objid, indexid, sort, override )
Rebuild an object's extent chain.
      Parameters
      ParamDescriptionValid Values
      dbid database ID
      objid object ID
      indexid index ID
      sort state of the sort bit0 sorted
      1not sorted
      override override option 0 rebuild only if database is not open
      1 rebuild even though database is already open

dbcc reindex ( tablename )
Rebuilds indexes.
      Parameters
      ParamDescription
      tablename table whose indexes are to be rebuilt

dbcc remap ( objid, dbname, 1 )
Remaps a stored proc, trigger, rule, default, or view to be compatible with
release 10.0.
      Parameters
      ParamDescription
      objid object ID
      dbname database name

dbcc report_al [( dbname )]
Report on differences between allocation bitmaps and page chains.
      Parameters
      ParamDescription
      dbname (optional) database name

dbcc resource
Print out the RESOURCE structure.

dbcc save_rebuild_log ( dbname )
Generate a new log containing a single checkpoint record and preserve old log
extents by changing the object ID.
      Parameters
      ParamDescription
      dbname database name

dbcc setkeepalive (#minutes)
For use on Novell TCP/IP only.

dbcc setreplicate ( replicate_name, dbccbit )
Update the replication status of the object in cache only.
      Parameters
      ParamDescriptionValid Values
      replicate_name object name
      dbccbit new status 0 false
      1 true

dbcc settrunc ( "ltm", option [, generation_num] )
Replication Server related: modifies LTM (Log Transfer Manager) information for
the database. "ltm" is entered as a literal string.
      Options
      OptionDescriptionValid Values
      truncation option Should log be allowed to be truncated. Can be either:
      "valid" set log truncation off -- The server cannot truncate tran log
      records that have not been transferred to Rep Server yet.
      "ignore" allow log to be truncated (via dump tran with no_log, etc.)
      generation id option resets the database generation number to
      generation_num. Used to increment number after a dump restore to prevent
      Rep Server from rejecting transactions as duplicates "gen_id",
      generation_num

dbcc show_bucket ( dbid, page, lookup_type )
Look for specified page in the buffer cache.
      Parameters
      ParamDescriptionValid Values
      dbid database ID
      page logical page number of page being looked for
      lookup_type how to conduct search 1 use hash algorithm to look in the
      bucket the page should be in
      2 scan the entire buffer cache

dbcc showtext ( db, page )
Displays text or image data contained on a specified page.
      Parameters
      ParamDescription
      db database name or database ID
      page logical page number

dbcc tab ( dbid, objid, printopt )
Prints out the contents of each data page for the given table.
      Parameters
      ParamDescriptionValid Values
      dbid database ID
      objid object ID
      printopt print option 0 print out only the page header (default)
      1 print out each row separately and the offset table
      2 print out the page as a whole and offset table

dbcc tablealloc ( tablename [, option][, mode] )
Check data and index pages against corresponding extent structures for a table.
      Parameters
      ParamDescriptionValid Values
      tablename table name or table ID
      option (optional) can take the following full fully check allocation
      optimized check allocation pages listed in OAM (default)
      fast exception report referenced but not allocated
      null as optimized
      mode (optional) can take the following fix fix errors on traversal
      (default for user tables)
      nofix do not fix errors (default for system tables)

dbcc thresholds ( db )
Provides thresholds information on the database.
      Parameters
      ParamDescription
      db database name or database ID

dbcc traceoff ( tracenum [, tracenum ] ... )
Turn off specified trace flags.
      Parameters
      ParamDescription
      tracenum number of the trace flag to turn off

dbcc traceon( tracenum [, tracenum ] ... )
Turn on specified trace flags.
      Parameters
      ParamDescription
      tracenum number of the trace flag to turn on

Trace flags can be the following:
      Trace Flags
      NumberDescription
      200 "Before" image of query tree
      201 "After" image of query tree
      260 To prevent "done_in_proc" messages
      302 Information in index selection
      310 Information in join selection
      317 Complete information in join selection (voluminous)
      320 Turn off join order heuristic
      1204 Print out deadlock chains and victim
      1205 Print out stack traces of deadlocked pids. Use in conjuction with
      1204
      2512 Bypass syslogs when doing checkalloc
      1603 Use only standard unix calls for disk I/O
      1610 Disable packet batching
      3300 Display each log record as it is processed by recovery (voluminous)
      3402 Do a alloc upgrade during loaddb recovery
      3604 Send dbcc output to screen
      3605 Send dbcc output to errorlog
      3607 Do not recover any database, clear tempdb, or startup checkpoint
      3608 Recover master only. Do not clear tempdb, or startup checkpoint
      3609 Recover all databases. Do not clear tempdb, or startup checkpoint
      3620 Do not kill infected processes
      3701 Allows dropping and creating of system indexes (DANGEROUS!)
      4000 Crash server at next loop for recovery testing
      4012 Boot without starting up checkpoint process
      4013 Place a record into errorlog each time someone logs in to the server
      5101 Engine 0 will perform all disk and network I/O.
      5102 Prevents engine 0 from running any non-affinitied tasks
      8399 Enable descriptive names for groups (dbcc monitor)

dbcc tune ( "parameter", value [, tablename] )
Sets buildmaster parameters. Probably not necessary as of System11, where the
old buildmaster parameters are now incorporated into sysconfigures (see
documentation on sp_configure).

dbcc undo ( dbid, pgnum, rownum )

dbcc update_tmode ( procname, tranmode )
Update transaction-mode in both sysobjects and DES for stored procedures.
      Parameters
      ParamDescriptionValid Values
      procname stored procedure name
      tranmode transaction mode unchained
      chained
      anymode

dbcc usedextents ( dbid, devtype, display )
Count or display the status of used/unused extents on specified devices.
      Parameters
      ParamDescriptionValid Values
      dbid database ID
      devtype type of device to check all show all extents
      log show extents of allocation for the log
      nonlog show extents of nonlog allocations
      display what to report on 0 information about the extents
      1 count of the extents

yethyeth

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
sql server未经确认的dbcc命令

相关英文：（2000和7中支持的dbcc命令）http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocDBCC.htmhttp://www.windowsitlibrary.com/Content/77/21/1.html 下面这些dbcc command有些是没有官方支持的，不建议初学者使用。切记小心使用，不过有很多还是非常
复制链接

扫一扫