SQL Server中的未文件化的dbcc命令(適用於SQL Server 2000)
DBCC is an abbreviation for Database Console Command. DBCC commands are generally used to check the physical and logical consistency of a database, although they are also used for a variety of miscellaneous tasks, as you will see here.
Note, the command:
DBCC TRACEON (3604)
is issued before each of the following DBCC examples in order to better demonstrate the effects of the command by displaying a trace of the output of the DBCC command. It is not actually required to run the DBCC commands examined below. If you run any of the DBCC commands below without the above option, the command runs, but you don't see what it is doing.
Syntax:
dbcc buffer ([dbid|dbname] [,objid|objname] [,nbufs], [printopt])
Note, the command:
DBCC TRACEON (3604)
is issued before each of the following DBCC examples in order to better demonstrate the effects of the command by displaying a trace of the output of the DBCC command. It is not actually required to run the DBCC commands examined below. If you run any of the DBCC commands below without the above option, the command runs, but you don't see what it is doing.
Undocumented DBCC commands
Here you can find some useful undocumented DBCC commands.[@more@]1. DBCC BUFFER
This command can be used to display buffer headers and pages from the buffer cache.Syntax:
dbcc buffer ([dbid|dbname] [,objid|objname] [,nbufs], [printopt])
where dbid|dbname - database id|database name objid|objname - object id|object name nbufs - number of buffers to examine 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 |
This is the example:
DBCC TRACEON (3604) dbcc buffer(master,'sysobjects') |
2. DBCC BYTES
This command can be used to dump out bytes from a specific address.
Syntax:
dbcc bytes ( startaddress, length )
where startaddress - starting address to dump length - number of bytes to dump |
This is the example:
DBCC TRACEON (3604) dbcc bytes (10000000, 100) |
3. DBCC DBINFO
Displays DBINFO structure for the specified database.
Syntax:
DBCC DBINFO [( dbname )]
where dbname - is the database name. |
This is the example:
DBCC TRACEON (3604) DBCC DBINFO (master) |
4. DBCC DBTABLE
This command displays the contents of the DBTABLE structure.
Syntax:
DBCC DBTABLE ({dbid|dbname})
where dbid|dbname - database name or database ID |
This is the example:
DBCC TRACEON (3604) DBCC DBTABLE (master) |
The DBTABLE structure has an output parameter called dbt_open. This parameter keeps track of how many users are in the database.
Look at here for more details:5. DBCC DES
Prints the contents of the specified DES (descriptor).
Syntax:
dbcc des [( [dbid|dbname] [,objid|objname] )]
where dbid|dbname - database id|database name. objid|objname - object id|object name |
This is the example:
DBCC TRACEON (3604) DBCC DES |
6. DBCC HELP
DBCC HELP returns syntax information for the specified DBCC statement. In comparison with DBCC HELP command in version 6.5, it returns syntax information only for the documented DBCC commands.
Syntax:
DBCC HELP ('dbcc_statement' | @dbcc_statement_var | '?')
This is the example:
DBCC TRACEON (3604) DECLARE @dbcc_stmt sysname SELECT @dbcc_stmt = 'CHECKTABLE' DBCC HELP (@dbcc_stmt) |
7. DBCC IND
Shows all pages in use by indexes of the specified table.
Syntax:
dbcc ind( dbid|dbname, objid|objname, printopt = {-2|-1|0|1|2|3} )
where dbid|dbname - database id|database name. objid|objname - object id|object name printopt - print option |
There is change in this command in how it is used in SQL Server 7.0, in that the printopt parameter is now no longer optional.
This is the example:
DBCC TRACEON (3604) DBCC IND (master, sysobjects, 0) |
8. DBCC log
This command is used to view the transaction log for the specified database.
Syntax:
DBCC log ( {dbid|dbname}, [, type={-1|0|1|2|3|4}] )
PARAMETERS: Dbid or dbname - Enter either the dbid or the name of the database in question. type - is the type of output: 0 - minimum information (operation, context, transaction id) 1 - more information (plus flags, tags, row length, description) 2 - very detailed information (plus object name, index name, page id, slot id) 3 - full information about each operation 4 - full information about each operation plus hexadecimal dump of the current transaction log's row. -1 - full information about each operation plus hexadecimal dump of the current transaction log's row, plus Checkpoint Begin, DB Version, Max XDESID by default type = 0 |
To view the transaction log for the master database, run the following command:
DBCC log (master)9. DBCC PAGE
You can use this command to view the data page structure.
Syntax:
DBCC PAGE ({dbid|dbname}, pagenum [,print option] [,cache] [,logical])
PARAMETERS: Dbid or dbname - Enter either the dbid or the name of the database in question. Pagenum - Enter the page number of the SQL Server page that is to be examined. Print option - (Optional) Print option can be either 0, 1, or 2. 0 - (Default) This option causes DBCC PAGE to print out only the page header information. 1 - This option causes DBCC PAGE to print out the page header information, each row of information from the page, and the page's offset table. Each of the rows printed out will be separated from each other. 2 - This option is the same as option 1, except it prints the page rows as a single block of information rather than separating the individual rows. The offset and header will also be displayed. Cache - (Optional) This parameter allows either a 1 or a 0 to be entered. 0 - This option causes DBCC PAGE to retrieve the page number from disk rather than checking to see if it is in cache. 1 - (Default) This option takes the page from cache if it is in cache rather than getting it from disk only. Logical - (Optional) This parameter is for use if the page number that is to be retrieved is a virtual page rather then a logical page. It can be either 0 or 1. 0 - If the page is to be a virtual page number. 1 - (Default) If the page is the logical page number. |
This is the example:
DBCC TRACEON (3604) DBCC PAGE (master, 1, 1) |
Look at here for more details:10. DBCC procbuf
This command displays procedure buffer headers and stored procedure headers from the procedure cache.
Syntax:
DBCC procbuf( [dbid|dbname], [objid|objname], [nbufs], [printopt = {0|1}] )
where dbid|dbname - database id|database name. objid|objname - object id|object name nbufs - number of buffers to print printopt - print option (0 print out only the proc buff and proc header (default) 1 print out proc buff, proc header and contents of buffer) |
This is the example:
DBCC TRACEON (3604) DBCC procbuf(master,'sp_help',1,0) |
11. DBCC prtipage
This command prints the page number pointed to by each row on the specified index page.
Syntax:
DBCC prtipage( dbid, objid, indexid, indexpage )
where dbid - database ID objid - object ID indexid - index ID indexpage - the logical page number of the index page to dump |
This is the example:
DBCC TRACEON (3604) DECLARE @dbid int, @objectid int SELECT @dbid = DB_ID('master') SELECT @objectid = object_id('sysobjects') DBCC prtipage(@dbid,@objectid,1,0) |
12. DBCC pss
This command shows info about processes currently connected to the server.
Syntax:
DBCC pss( suid, spid, printopt = { 1 | 0 } )
where suid - server user ID spid - server process ID printopt - print option (0 standard output, 1 all open DES's and current sequence tree) |
This is the example:
DBCC TRACEON (3604) dbcc pss |
13. DBCC resource
This command shows the server's level RESOURCE, PERFMON and DS_CONFIG information. RESOURCE shows addresses of various data structures used by the server. PERFMON structure contains master..spt_monitor field info. DS_CONFIG structure contains master..syscurconfigs field information.
Syntax:
DBCC resource
This is the example:
DBCC TRACEON (3604) DBCC resource |
14. DBCC TAB
You can use the following undocumented command to view the data pages structure (in comparison with DBCC PAGE, this command will return information about all data pages for viewed table, not only for particular number).
Syntax:
DBCC tab (dbid, objid)
where dbid - is the database id objid - is the table id |
This is the example:
DBCC TRACEON (3604) DECLARE @dbid int, @objectid int SELECT @dbid = DB_ID('master') SELECT @objectid = object_id('sysdatabases') DBCC TAB (@dbid,@objectid) |
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/37724/viewspace-1021255/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- SQL Server中的dbcc page命令用法SQLServer
- SQL Server中的dbcc ind命令用法SQLServer
- Sql Server系列:DBCC命令SQLServer
- 一些文件中沒有介紹的SQL Server DBCC命令SQLServer
- SQL SERVER DBCC命令解釋SQLServer
- 得到SQL Server 2008的包括未文件化的所有dbcc命令列表及其語法SQLServer命令列
- SQL Server資料庫管理員必備的DBCC命令SQLServer資料庫
- sql server dbcc常用命令使用詳解SQLServer
- sql server中的hash應用優化SQLServer優化
- SQL SERVER中SQL優化SQLServer優化
- [zt] SQL Server管理員必備的DBCC用法SQLServer
- SQL Server CE和SQL Server 2000/2005中的ISNULL函式的異同SQLServerNull函式
- 利用DBCC PAGE檢視SQL Server中的表和索引資料SQLServer索引
- SQL Server 2000中的觸發器使用SQLServer觸發器
- 用sql語句dbcc log 檢視SQL Server 資料庫的事務日誌SQLServer資料庫
- SQL Server Express和SQL Server Compact的應用SQLServerExpress
- 優化SQL Server中的備份優化SQLServer
- SQL Server 2000 中查詢優化器使用的統計SQLServer優化
- SQL Server中,WITH AS的使用SQLServer
- [zt] 揭祕SQL Server 2000中的Bookmark LookupSQLServer
- SQL Server 2000中的資料同步問題SQLServer
- SQL Server 2005和SQL Server 2000資料的相互匯入SQLServer
- SQL Server 2000 shrink tempdbSQLServer
- 適用於SQL Server生產環境DBA的七大技巧SQLServer
- 不同網段sql server 2005 連線 sql server 2000的問題SQLServer
- sql server 中的一些實用的sql語句SQLServer
- 修改Delphi使之適應SQL Server2000 (轉)SQLServer
- XML文件插入SQL Server2000資料庫(2)XMLSQLServer資料庫
- XML文件插入SQL Server2000資料庫(1)XMLSQLServer資料庫
- 【轉帖】認識SQL Server2000 中的CheckPointSQLServer
- SQL Server 2000的安全策略(一)SQLServer
- SQL Server 2000的安全策略(二)SQLServer
- SQL Server2000的OpenXML特性 (轉)SQLServerXML
- SQL Server中@@ROWCOUNT的用法SQLServer
- SQL server中的NULL值SQLServerNull
- SQL Server中distinct的用法SQLServer
- SQL Server中tempdb的管理SQLServer
- 關於SQL Server 2000的日誌檔案壓縮SQLServer