Undocumented DBCC Commands

for RuBoard

The DBCC command originally exposed a small cadre of database maintenance routines that were outside the realm of traditional Transact -SQL. DBCC orga nized these routines into a versatile database administration Swiss army knife that DBAs used to manage routine maintenance and consistency checking.

Since then, DBCC's command verb list has grown to include dozens of things not related to database error checking. DBCC now handles everything from Profiler audit messages to database consistency checking to full-text index management. Many of the things DBCC can do are undocumented and are only called by Microsoft-supplied code. We'll talk about a few of them in a moment.

Before we delve into DBCC undocumented command verbs, here are a few things to keep in mind:

Include the WITH NO_INFOMSGS option to limit DBCC output to error messages. This makes the output from verbose commands like DBCC CHECKALLOC much more manageable without losing critical info .

DBCC HELP( commandverb ) lists usage information for many DBCC command verbs. Naturally, most of the undocumented commands aren't listed, but it's still a good idea to check.

Use DBCC TRACEON(3604) to route DBCC output back to you rather than to the system console or error log. Although many of the undocumented commands send their output to the error log by default, you can route them to your client connection by enabling trace flag 3604.

Table 22-1. Undocumented DBCC Command Verbs

DBCC command verb and purpose		Example
ADDEXTENDEDPROC(procname,DLL) Adds an extended procedure to the list maintained by the server. It has the same basic functionality as the sp_addextendedproc stored procedure and is, in fact, called by the procedure. The procname parameter is the name of the extended procedure and DLL is the name of the DLL in which it resides.		DBCC ADDEXTENDEDPROC('xp_mode','xp_stats.dll')
ADDINSTANCE(object,instance) Adds an object instance to track in Performance Monitor. Stored procedures that initialize Performance Monitor counters use this to set up various areas of SQL Server for performance tracking. object is the name of the object that contains the instance (e.g., "SQL Replication Agents"). instance is the name of the instance to add (e.g., "Logreader").		DBCC ADDINSTANCE("SQL Replication Agents", "Snapshot")
AUDITEVENT(id, subclass, succeeded, loginame, username, grpname, sid) Generates a Security Audit trace event. You can trace Security Audit events in Profiler. Id is the event number you wish to generate. Subclass is the ID of the specific event subclass you wish to generate. Loginame is the login attempting the audited event. Username is the user name of the database user attempting the event. Grpname is the name of the group or role to which the user belongs. Sid is the security ID number of the login attempting the audited action.		DBCC AUDITEVENT (109, 1, 0, @loginame, @name_in_db, @grpname , NULL) /* Valid id/subclass values: ID Sub Event 104 1 Add login 104 2 Drop login 105 1 Grant login 105 2 Revoke login 105 3 Deny login 106 1 Change default database 106 2 Change default language 107 1 User change password 107 2 Admin. change password 108 1 Add server role member 108 2 Drop server role member 109 1 Add DB user 109 2 Drop DB user 109 3 Grant DB access 109 4 Revoke DB access 110 1 Add DB role member 110 2 Drop DB role member 110 3 Change DB role member 111 1 Add role 111 2 Drop role 112 1 App role change password */
BCPTABLOCK(dbid, tabid, setflag) Sets the table lock on bulk load option for a table. (It's called by sp_tableoption.) This can improve performance for bulk inserts because it avoids setting a row lock for every inserted row. Dbid is the database ID, tabid is the table's object ID, and setflag is a 1 or 0 indicating whether to set the option.		DECLARE @dbid int, @objid int SELECT @dbid=DB_ID('pubs'), @objid=OBJECT_ID('titles') DBCC BCPTABLOCK(@dbid,@objid,1)
BUFFER(dbid[,objid][,numberofbuffers][,printopt { 0 1 2}]) Used to dump the contents of SQL Server memory buffers. Buffers can be listed for a specific object or for an entire database.		DECLARE @dbid int, @objid int SELECT @dbid=DB_ID('pubs'), @objid=OBJECT_ID('pubs..titles') SELECT COUNT(*) FROM pubs..titles -- Load buf DBCC TRACEON(3604) DBCC BUFFER(@dbid,@objid,1,2)
BYTES(startingaddress,length) Lists the contents of the memory area beginning at startingaddress for length bytes. The address specified must be a valid address within the SQL Server process space.		DBCC BYTES(0014767000,50)
CALLFULLTEXT(funcid[ catid][,objid]) Valid function ID values		USE master GO IF OBJECT_ID('sp_fulltext_resource') IS NOT NULL DROP PROC sp_fulltext_resource GO CREATE PROC sp_fulltext_resource @value int -- value for 'resource_usage' AS DBCC CALLFULLTEXT(9,@value) -- FTSetResource (@value) IF (@@error<>0) RETURN 1 -- SUCCESS -- RETURN 0 -- sp_fulltext_resource GO EXEC sp_MS_marksystemobject 'sp_fulltext_resource' EXEC sp_fulltext_resource 3
ID	Function (Parameters)
1	Creates a catalog (Catalog ID, path )
2	Drops a catalog (Catalog ID)
3	Populates a catalog (Catalog ID, 0 = full, 1 = incremental)
4	Stops a catalog population (Catalog ID)
5	Adds a table for FT indexing (Catalog ID, Object ID)
6	Removes a table from FT indexing (Catalog ID, Object ID)
7	Drops all catalogs (Database ID)
8	Performs catalog clean-up
9	Specifies the level of CPU resources allocated to Microsoft Search (Resource value: 15; 1 = background, 5 = dedicated default: 3)
10	Sets FT connection timeout (Timeout value in seconds: 132,767)
Used to perform a variety of full-text-related functions. Funcid specifies what function to perform and what parameters are valid. Catid is the full-text catalog ID. Objid is the object ID of the affected object. Note that CALLFULLTEXT is only valid within a system stored procedure. This procedure must have its system bit set (see the undocumented procedure sp_MS_marksystemobject for more info) and its name must begin with "sp_fulltext_."
DBCONTROL(dbname,option) Sets database options. Performs many of the functions of sp_dboption and is, in fact, called by the procedure. Dbname is the name of the database. Option is a token specifying the option to set.		/* Supported options multi Specifies multi-user mode offline Takes database offline online Brings database back online readonly Makes database readonly readwrite Makes database readwrite single Specifies single-user mode */ DBCC DBCONTROL('pubs',multi)
DBINFO(dbname) Lists system-level information about the specified database, including its creation date, ID, status, next timestamp value, and so forth.		DBCC DBINFO('pubs')
DBRECOVER(dbname) Manually recovers a database. Normally, databases are recovered at system start-up. If this did not occurbecause of an error or because recovery was disabled (see trace flags 3607 and 3608DBCC DBRECOVER can be used to attempt a manual recovery. Dbname is the name of the database to recover.		DBCC DBRECOVER('pubs')
DBREINDEXALL(dbname) Rebuilds all the indexes in the current database. Only works for user (nonsystem) databases		DBCC DBREINDEXALL('pubs') WITH NO_INFOMSGS
DBCC DBTABLE(dbid) Lists DBT (DB Table) and FCB (File Control Block) information for the specified database.		DECLARE @dbid int SET @dbid=DB_ID('pubs') DBCC DBTABLE(@dbid)
DELETEINSTANCE(object,instance) Deletes a Performance Monitor object instance previously set up with DBCC ADDINSTANCE. Object is the name of the Performance Monitor object. Instance is the name of the instance to delete. Specify a wildcard for instance to delete multiple instances.		DBCC DELETEINSTANCE("SQL Replication Merge", "%")
DES(dbid,objid) Lists system-level descriptive information for the specified object.		DECLARE @dbid int, @objid int SELECT @dbid=DB_ID('pubs'), @objid=OBJECT_ID('authors') DBCC DES(@dbid, @objid)
DETACHDB(dbname) Detaches a database from the server. The database can then be moved to another server and can be reattached with sp_attach_db. This function is called by the sp_detach_db stored procedure.		DBCC DETACHDB('northwind2')
DROPEXTENDEDPROC(procname) Drops an extended procedure. It's called by sp_dropextendedprocedure.		USE master DBCC DROPEXTENDEDPROC('xp_mode')
ERRORLOG Closes the current error log and starts another one, cycling the file extensions similarly to a server restart. It's called by the sp_cycle_errorlog stored procedure.		DBCC ERRORLOG
EXTENTINFO(dbname, tablename, indid) Lists extent information for all the extents belonging to an object. Dbname is the name of the database, tablename is the name of the table, and indid is the index ID of the index to list.		DBCC EXTENTINFO('pubs','titles',1)
FLUSHPROCINDB(dbid) Forces a recompile of all the stored procedures in a database. Dbid is the database ID of the target database. This is handy when you've changed an option in the database that would materially affect the queries generated for its stored procedures. Sp_dboption, for example, uses DBCC FLUSHPROCINDB to ensure that changes to compile-time options are accommodated by a database's stored procedures.		DECLARE @dbid int SET @dbid=DB_ID('pubs') DBCC FLUSHPROCINDB(@dbid)
IND(dbid, objid[,indid]) Lists system-level index information for the specified object.		DECLARE @dbid int, @objid int SELECT @dbid=DB_ID('pubs'), @objid=OBJECT_ID('pubs..authors') DBCC IND(@dbid,@objid, 1)
INVALIDATE_TEXTPTR(@TextPtrVal) Invalidates the specified in-row text pointer in the transaction. If @TextPtrVal is NULL, invalidates all in-row text pointers in the current transaction. Called by sp_invalidate_textptr.		CREATE TABLE #testtxt (c1 int, c2 text) EXEC tempdb..sp_tableoption '#testtxt', 'text in row', 'on' INSERT #testtxt VALUES ('1','Text lives here') BEGIN TRAN DECLARE @ptr varbinary(16) SELECT @ptr = TEXTPTR(c2) FROM #testtxt READTEXT #testtxt.c2 @ptr 0 5 DBCC INVALIDATE_TEXTPTR(@ptr) READTEXT #testtxt.c2 @ptr 0 5 -- Fails COMMIT TRAN
LOCKOBJECTSCHEMA (objname) Blocks schema changes by other connections until the caller commits the current transaction. It also increments the schema_ver column in sysobjects. This command has no effect if executed outside a transaction.		USE pubs BEGIN TRAN DBCC LOCKOBJECTSCHEMA('titleauthor')
LOG(dbid) Displays log record information from the current database's transaction log. You can use INSERT..EXEC() to trap this output in a table for further processing.		CREATE TABLE #logrecs (CurrentLSN varchar(30), Operation varchar(20), Context varchar(20), TransactionID varchar(20)) INSERT #logrecs EXEC('DBCC LOG(''pubs'')')
MEMORYSTATUS Provides detailed info about SQL Server memory use.		DBCC MEMORYSTATUS
NO_TEXTPTR(@TabId, @InlineSize) Marks a table as not supporting text pointers (16-byte pointers to text pages), thus allowing in-row text. @TabId is the object ID of the table. @InlineSize is the number of characters (247,000) to store inline. Called by sp_tableoption.		CREATE TABLE testtxt (c1 int, c2 text) DECLARE @TabId int SET @TabId=OBJECT_ID('testtxt') DBCC NO_TEXTPTR(@TabId,500) INSERT testtxt VALUES ('1','Text lives here') BEGIN TRAN DECLARE @ptr varbinary(16) SELECT @ptr = TEXTPTR(c2) FROM testtxt READTEXT testtxt.c2 @ptr 0 5 COMMIT TRAN
PAGE (dbiddbname, filenum, pagenum [,printopt])		DBCC TRACEON(3604) GO DBCC PAGE('pubs',1,70,2)
Value	Meaning
	(Default)-Print the page and buffer headers
1	Print the page and buffer headers, each row of the table, and the row offset table
2	Print the page and buffer headers, the page itself, and the row offset table
Dumps the contents of a specific database page. dbiddbname is the ID or name of the database, filenum is the database file number containing the page, pagenum is the number of the page, and printopt specifies what to print.
PRTIPAGE(dbid, objid, indexid[, printopt { 0 1 2}]) Lists page information for the specified index		DECLARE @dbid int, @pagebin varchar(12), @pageid int, @fileid int, @objid int SELECT TOP 1 @dbid=DB_ID('pubs'), @objid=id, @pagebin=first FROM pubs..sysindexes WHERE id=OBJECT_ID('pubs..authors') EXEC sp_decodepagebin @pagebin, @fileid OUT, @pageid OUT DBCC PRTIPAGE(@dbid, @objid, 2, @pageid)
PSS Dumps the process status structure (PSS) for a given connection. There is one PSS for every connection on the server, including system connections. The PSS includes info such as the transaction control block, the isolation level, and a host of other useful info.		DBCC PSS
RESOURCE Lists resource utilization information for the server.		DBCC TRACEON(3604) DBCC resource DBCC TRACEOFF(3604)
SETINSTANCE(object,counter,instance,val) Sets the value of a Performance Monitor instance counter. You can use this when benchmarking query and stored procedure performance to set a user-definable counter inside Performance Monitor. In fact, this is how the sp_user_counternn procedures workthey call DBCC SETINSTANCE. Object is the name of the Performance Monitor object, instance is the name of the object's instance to adjust, counter is the name of the performance counter to change, and val is the new value of the counter.		DBCC SETINSTANCE('SQLServer:User Settable', 'Query', 'User counter 1', 3)
STACKDUMP Dumps the call stacks of all active connections, including their input buffers. Part of this info ends up in the error log; the rest is placed in a file with a .DMP extension in the SQL Server log folder.		DBCC STACKDUMP
TAB(dbid,objid[,printopt { 0 1 2}}]) Lists system-level information for the specified table.		DECLARE @dbid int, @objid int SELECT @dbid=DB_ID('pubs'), @objid=OBJECT_ID('pubs..authors') DBCC TAB(@dbid, @objid, 2)
UPGRADEDB(dbname) Upgrades the system objects in the specified database to the current version of the database engine.		DBCC UPGRADEDB('oldpubs')

Undocumented Functions

Before the advent of UDFs, using undocumented functions was particularly tempting because if Transact-SQL didn't have a function you needed, there were certain things you simply couldn't do. With the addition of UDFs in SQL Server 2000, there's not nearly as much reason to use undocumented routines. In most cases you should be able to "roll your own" to provide functionality not found in Transact-SQL's documented routines.

That said, there may be times when undocumented functions provide capabilities or system internals info that you can't access by any other means. What follows in Table 22-2 is a list of some undocumented Transact-SQL functions. As I said before, use them at your own risk.

Table 22-2. Undocumented Transact-SQL Functions

Function and purpose	Example
@@MICROSOFTVERSION Returns an internal tracking number used by Microsoft.	SELECT @@MICROSOFTVERSION ----------- 117441211
ENCRYPT(string) Encrypts a string. It's used internally by the server to encrypt Transact-SQL code stored in syscomments (when WITH ENCRYPTION is specified).	SELECT ENCRYPT('VALET') ------------------------------------------ 0x4C0059004E00410052004400
GET_SID(username) Returns the current NT system ID for a specified user or group name as a varbinary(85). Prefix username with \U to search for an NT user ID; prefix it with \G to search for an NT group ID. Note that this function only works within system stored procedures that have their system bit set. See the undocumented procedure sp_MS_marksystemobject for more information.	USE master GO IF (OBJECT_ID('sp_get_sid') IS NOT NULL) DROP PROC sp_get_sid GO CREATE PROCEDURE sp_get_sid @loginame sysname AS DECLARE @sid varbinary(85) IF (charindex('\ ', @loginame) = 0) SELECT SUSER_SID(@loginame) AS 'SQL User ID' ELSE BEGIN SELECT @sid=get_sid('\ U'+@loginame, NULL) IF @sid IS NULL SELECT @sid=get_sid('\ G'+ @loginame, NULL) -- Maybe it's a group IF @sid IS NULL BEGIN RAISERROR('Couldn''t find an ID for the specified loginame',16,10) RETURN -1 END ELSE SELECT @sid AS 'NT User ID' RETURN 0 END GO EXEC sp_MS_marksystemobject 'sp_get_sid' EXEC sp_get_sid 'LEX_TALIONIS\ KHEN'
OBJECT_ID(..,'local') Although the OBJECT_ID() function itself is of course documented, its optional second parameter isn't. Because you can pass a fully qualified object name as the first argument, OBJECT_ID() can return ID numbers for objects that reside in databases other than the current one. There may be times when you want to prevent this. For example, if you're performing a task on an object that requires access to catalog information in the current database, you may need to ensure that the object name not only translates to a valid object ID, but that it's also a local object. Pass 'local' as OBJECT_ID()'s second parameter to ensure that it sees objects in the current database only.	USE pubs SELECT OBJECT_ID('Northwind..Orders'), OBJECT_ID('Northwind..Orders','local') ----------- ----------- 357576312 NULL
PLATFORM() Returns an integer representing the operating system and version of SQL Server on which you're running.	SELECT PLATFORM() ----------- 1025
PWDCOMPARE(str,pwd,oldenc) Compares a string with an encrypted password. Str is the string to compare, pwd is the encrypted password to use, and oldenc is a 1 or 0 indicating whether old-style encryption was used to encrypt pwd. You can retrieve an encrypted password directly from the sysxlogins password column, or you can use the undocumented PWDENCRYPT() function to create one from a string.	SELECT PWDCOMPARE('enmity', password, (CASE WHEN xstatus&2048=2048 THEN 1 ELSE 0 END)) FROM sysxlogins WHERE name='k_reapr' ----------- 1
PWDENCRYPT(str) Encrypts a string using SQL Server's password encryption algorithm. Stored procedures that manage SQL Server passwords use this function to encrypt user passwords. You can use the undocumented PWDCOMPARE() function to compare an unencrypted string with the return value of PWDENCRYPT().	SELECT PWDENCRYPT('vengeance') AS EncryptedString,PWDCOMPARE('vengeance', PWDENCRYPT('vengeance'), 0) AS EncryptedCompare EncryptedString EncryptedCompare EncryptedString EncryptedCompare ---------------- ---------------- -------- 1
TSEQUAL(ts1,ts2) Compares two timestamp or rowversion values. Returns 1 if they're identical; raises an error if they're not. The TSEQUAL() function has been around for years . It dates back to the days when Microsoft SQL Server was merely an OS/2 port of Sybase SQL Server. It's not used as often any more, mainly because it's no longer necessary. You can compare two timestamp/rowversion columns directly and decide for yourself whether to raise an error. There's also no performance advantage to using TSEQUAL rather than a simple equality comparison. Still, it's not documented in the Books Online, so I'm compelled to include it here.	USE tempdb CREATE TABLE #testts (k1 int identity, rowversion rowversion) DECLARE @ts1 rowversion, @ts2 rowversion SELECT @ts1=@@DBTS, @ts2=@ts1 SELECT CASE WHEN TSEQUAL(@ts1, @ts2) THEN 'Equal' ELSE 'Not Equal' END INSERT #testts DEFAULT VALUES SET @ts2=@@DBTS SELECT CASE WHEN TSEQUAL(@ts1, @ts2) THEN 'Equal' ELSE 'Not Equal' END GO DROP TABLE #testts --------- Equal Server: Msg 532, Level 16, State 2, Line 16 The timestamp (changed to 0x0000000000000093) shows that the row has been updated by another user.
UNCOMPRESS() Uncompresses a string.	SELECT CAST(CASE WHEN ([status] & 2 = 2) THEN (UNCOMPRESS([ctext])) ELSE [ctext] END AS nvarchar(4000)) FROM syscomments WHERE ID=OBJECT_ID('sp_helptext')

for RuBoard