DBCC
1、dbcc memorystatus 还没有找到详细的文档说明 2、DBCC FreeProcCache 相当于oracle中的alter system flush share pool,请会清空目前数据库的所有内存,使得系统恢复最新状态 3、dbcc traceon(3604) dbcc page (pubs,1,15,3) 将会显示在15页中的pubs数据库中的对于表的内容 4、DBCC SHOWCONTIG ('nulltable',0) 将显示 nulltable 表的内容,0表示不显示索引内容,1为显示 5、dbcc extentinfo('pubs','nulltable',0) 显示对于库表的extent的信息,0表示不显示索引内容,1为显示 也可以将显示结果存到数据库中去: INSERT INTO extentinfo exec('dbcc extentinfo(''pubs'',''nulltable'',0)') 6、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, which includes: 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 an example: DBCC TRACEON (3604) DBCC buffer(master,'sysobjects') 7、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 an example: DBCC TRACEON (3604) DBCC bytes (10000000, 100) 8、DBCC DBINFO Displays DBINFO structure for the specified database. Syntax: DBCC DBINFO [(dbname)] where: dbname - is the database name This is an example: DBCC TRACEON (3604) DBCC DBINFO (master) 9、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 an 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. 10、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 an example: DBCC TRACEON (3604) DECLARE @dbcc_stmt sysname SELECT @dbcc_stmt = 'CHECKTABLE' DBCC HELP (@dbcc_stmt) 11、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 or the database name objid|objname - object id or the 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 an example: DBCC TRACEON (3604) DBCC IND (master, sysobjects, 0) 12、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}]) where: dbid or dbname - Enter either the dbid or the name of the database type - is the type of output, and includes these options: 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) 13、DBCC PAGE You can use this command to view the data page structure. Syntax: DBCC PAGE ({dbid|dbname}, pagenum [,print option] [,cache] [,logical]) where: dbid|dbname - Enter either the dbid or the name of the database 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 an example: DBCC TRACEON (3604) DBCC PAGE (master, 1, 1) 14、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 or the database name objid|objname - object id or the 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 an example: DBCC TRACEON (3604) DBCC procbuf(master,'sp_help',1,0) 15、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 an 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) 16、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 an example: DBCC TRACEON (3604) DBCC pss 17、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 an example: DBCC TRACEON (3604) DBCC resource 18、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 an example: DBCC TRACEON (3604) DECLARE @dbid int, @objectid int SELECT @dbid = DB_ID('master') SELECT @objectid = object_id('sysdatabases') DBCC TAB (@dbid,@objectid)