Undocumented DBCC Commands | ppk on JavaScript, 1/e

The DBCC commands in this section are undocumented and are not supported by Microsoft. With that said, using these DBCC commands, you can unleash some of the power that the SQL Server tools do not offer.

Note

Some DBCC commands have been skipped in this section. The ones that have been skipped are installed for backward compatibility or are unneeded.

Before you run a lot the DBCC commands, it's helpful to turn on the trace flag 3604, which will route messages to the client instead of the error log. Another item worth mentioning is that you can find out more about the available options for a given DBCC command by using the DBCC HELP command as shown here:

DBCC HELP (CHECKDB)

In the above scenario, you're looking up help on the DBCC CHECKDB command. This will output the following results:

CHECKDB [('database_name'[, NOINDEX | REPAIR])]  [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, PHYSICAL_ONLY] [, ESTIMATEONLY][, TABLOCK]] DBCC execution completed. If DBCC printed error messages,  contact your system administrator.

Often, you'll find that no help will be available for a given command. If no information is available, you'll receive the following message:

Server: Msg 8987, Level 16, State 1, Line 1 No help available for DBCC statement 'BUFFER'. DBCC execution completed. If DBCC printed error  messages, contact your system administrator.

Some of the DBCC commands listed in this section are relatives to similar stored procedures or extended stored procedures. In these cases, always use the stored procedure or extended stored procedure because the error messages are better and they are documented.

Tip

In SQL Server 7.0, you had to call the database in most DBCC commands with the dbid. In SQL Server 2000, Microsoft has added the ability to use the database name instead of the dbid. In the examples in this section, I'm continuing to use the dbid to preserve backward compatibility with your 7.0 databases where it's needed.

Without further ado, let's dive into the commands.

DBCC BUFFER ([dbid|dbname] [,objid|objname] [,nbufs], [printopt])

DBCC BUFFER will print the current buffer headers and pages from the buffer cache for an individual object. You can execute the command by using the following syntax:

DBCC BUFFER(northwind,'orders')

There are two optional parameters for this command. You can use nbufs to specify a number of buffers to examine. The printopt option will format the display in a number of ways. Valid options for this parameter are

0 = Print out only the buffer header and page header. This is the default option if nothing is specified.
1 = Print out each row separately and the offset table.
2 = Print out the entire row and the offset table.

DBCC BYTES (startaddress, length)

DBCC BYTES will dump the memory for an area beginning at the startaddress parameter for the specified length. To run the stored procedure, you can use the following syntax:

DBCC BYTES (3000090, 100)

DBCC DBINFO (dbname)

DBCC DBINFO will display lots of valuable information about the specified database. This would include information about when the database was created and the next timestamp. Most of the information here can be gathered more easily by querying the system tables. To run the command, use the following syntax:

DBCC TRACEON (3604) DBCC DBINFO (Northwind)

DBCC DBRECOVER (dbname)

DBCC DBRECOVER will manually recover a database if recovery fails at SQL Server's startup. You can utilize this if you've corrected the problem that caused the database recovery problem. To run the command, use the following syntax:

DBCC DBRECOVER (Northwind)

DBCC DBTABLE (dbname)

DBCC DBTABLE will output extended information about your database and each file that makes up the database. It will display information such as the next log ID LSN used for transaction log backups. To use the command, you can use the following syntax:

DBCC DBTABLE (Northwind) DBCC DBTABLE (Northwind)

DBCC DES (dbname|dbid, objname|objid)

DBCC DES prints the contents of the system-level descriptor (DES) for an object. To use the command, use the following syntax:

DBCC DBTABLE (Northwind) DBCC DES(Northwind, 'categories')

DBCC DROPCLEANBUFFERS

DBCC DROPCLEANBUFFERS will purge the data cache from the server. This is a great command when you're running benchmarks and need to test disk I/O without caching. This can be used in conjunction with DBCC FREEPROCCACHE, which is a documented DBCC command that will flush the procedure cache. To run the command, use the following syntax:

DBCC DROPCLEANBUFFERS

DBCC ERRORLOG

DBCC ERRORLOG will close the current error log and cycle it to the archives. To execute the command, you won't need any parameters:

DBCC ERRORLOG

DBCC EXTENTINFO (dbame, tablename, indid)

DBCC EXTENTINFO will output information about the extents for an object. To execute the command, use the following syntax:

DBCC EXTENTINFO(Northwind, 'orders', 1)

DBCC FLUSHPROCINDB (dbid)

DBCC FLUSHPROCINDB will recompile every stored procedure in a given database at once. The command is one of the few that still require the database ID. To find out the ID of a database, the simplest method is to use the db_id() function. To run the command, use the following syntax:

DECLARE @dbid int SELECT @dbid = DB_ID('Northwind') DBCC FLUSHPROCINDB (@dbid)

DBCC IND (dbid|dbname, objid|objname, [printopt = { 0 | 1 | 2 }])

DBCC IND will display low-level system information about a given object. To execute the command, use the following syntax:

DBCC IND (Northwind, 'orders', 1)

DBCC LOCKOBJECTSCHEMA (objectname)

DBCC LOCKOBJECTSCHEMA will prevent anyone else from modifying the schema of an object until you commit your transaction. All other connections will have to wait until the COMMIT statement is issued. This is another type of locking method you can utilize when loading bulk amounts of data. To execute the command, use the following syntax:

USE Northwind BEGIN TRAN DBCC LOCKOBJECTSCHEMA ('categories') --Your core query here. COMMIT TRAN

DBCC LOG ({dbid|dbname}, [, type={-1|0|1|2|3|4}])

DBCC LOG prints out information about the transaction log. This is the best method of reading the transaction log with the tools provided in SQL Server. Some of the information you can determine from this command includes:

Current LSN
Object modified and index used
Type of logged transaction
Transaction ID

To execute the command, use the following syntax:

DBCC LOG(Northwind)

The type parameter is an additional setting you can use to get more information about the logged action. The parameters run from -1 to 4 and perform the following functions:

-1 = Everything that option 4 has and the Checkpoint Begin, DB Version, and Max XACTID.
0 = Minimum displayable information. This option is the default if no options are specified.
1 = Slightly more information, including flags, tags, and row length.
2 = More information than the 1 option. This option includes the object name, index name, page ID, and slot ID.
3 = Full information about the logged event.
4 = Full information about the logged event plus the hexadecimal dump.

DBCC PAGE ({dbid|dbname}, filenumber, pagenum [,print option] [,cache] [,logical])

DBCC PAGE will dump the contents of a given data page. To run the command, you'll need to know the data page number you'd like to dump and the file number that the data page is on. To execute the command, use the following syntax:

DBCC TRACEON (3604) DBCC PAGE ('Northwind', 1, 70, 1)

You also can use extended print options to display additional information:

0 = The default option that will print the page and buffer headers.
1 = Prints the page and buffer headers as well as each row from the table and the row offset table.
2 = Prints the page and buffer headers as well as the page and the row offset table.

DBCC PRTIPAGE (dbid, objid, indexid, indexpage)

DBCC PRTIPAGE will output detailed information about an index. To execute the command, use the following syntax and replace the database and table name with your information:

DBCC TRACEON (3604) DECLARE @dbid int, @objectid int SELECT @dbid = DB_ID('Northwind') SELECT @objectid = object_id('Orders') DBCC PRTIPAGE(@dbid,@objectid,1,0) DBCC TRACEOFF (3604)

DBCC RESOURCE

DBCC RESOURCE lists valuable resource utilization information for your system. To execute the command, use the following syntax (you will need to also turn on trace flag 3604 to view the output of the query):

DBCC TRACEON (3604) DBCC RESOURCE

DBCC SETINSTANCE (object, counter, instance, value)

DBCC SETINSTANCE will set a user-defined counter to any value. SQL Server uses this internally to set performance counters, and you can use it to monitor the performance of an individual SQL Server process you've set up. To execute the command, use the following syntax:

DBCC SETINSTANCE ("SQLServer:User Settable", "Query","User Counter 1", 40)

DBCC SQLPERF (command)

DBCC SQLPERF accepts one parameter that can either show you the amount of available free log space in your database or the information about the wait types. The two commands I commonly see are LOGSPACE and WAITSTATS, as shown in this example syntax:

DBCC SQLPERF(LOGSPACE)

DBCC TAB (dbid, objid)

DBCC TAB outputs low-level system information about a table. To execute the command, use the following syntax:

DBCC TRACEON (3604) DECLARE @dbid int, @objectid int SELECT @dbid = DB_ID('Northwind') SELECT @objectid = object_id('orders') DBCC TAB (@dbid,@objectid) DBCC TRACEOFF (3604)

DBCC UPGRADEDB (dbname)

DBCC UPGRADEDB is used to upgrade a database to the most recent version of the database engine. To execute the command, use the following syntax:

DBCC UPGRADEDB(Northwind)