|
sql server未经确认的dbcc命令
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 Databasename|dbid - Identifier for the database you wish to view the log on. ResponseOption - 0 to 4 for the level of report you need. 0 is default. 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 command有些是没有官方支持的，不建议初学者使用。切记小心使用，不过有很多还是非常有用，有兴趣的可以测试看看！ 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 |
|
|
