VN:F [1.7.9_1023]
Rating: 0.0/5 (0 votes cast)

Two years ago I wrote an article about undocumented DBCC commands in SQL Server 2005 for Polish Microsoft TechNet website (the article is in Polish). Yesterday I decided to check if I’m able to make a list of all DBCCs available on SQL Server 2008.

There are only about 30 DBCC commands described in SQL Server 2008 Books Online. Imagine that the full list contains 101 of them (and on SQL Server 2005 the number is even greater). Why Microsoft folks do not document so many commands (some of undocumented DBCCs are quite useful)? Honestly, I have no idea :-)

Fortunately, you can get the full list of DBCC commands easily and play with them on your own. Moreover, you can even get a syntax for each of those commands. So what should you do?

First of all you have to turn on the trace flag:

DBCC TRACEON(2588);

NOTE: On SQL Server 2005 and 2008 the flag number is 2588. On SQL Server 2000 the number is 2520.

Then you can get a full list of DBCCs by executing the following command:

DBCC HELP('?') WITH NO_INFOMSGS;

The result should be a list of all DBCC commands – both documented and undocumented.

NOTE: The DBCC HELP command can be used either to return a list of DBCC commands or to return a syntax of an individual command.

Now, save the result as a file (you can do it easily with SQL Server Management Studio by right-clicking in the Messages window and choosing Save Results As… option).

Assuming you saved the file to C:\dbcc.rpt (if not, change the path respectively in the code) you can use BULK INSERT statement to import the DBCC command names from the file into a temporary table:

IF OBJECT_ID('tempdb.dbo.#dbccs') IS NOT NULL
  DROP TABLE #dbccs;
GO
CREATE TABLE #dbccs(
  [dbcc] nvarchar(4000) NOT NULL);
GO
DECLARE @bulkcmd varchar(8000);
SET @bulkcmd = 'BULK INSERT #dbccs
FROM N''C:\dbcc.rpt''
WITH(
  ROWTERMINATOR = ''' + NCHAR(10) + NCHAR(13) + NCHAR(10) + ''',
  DATAFILETYPE = ''widechar''
)';
EXEC(@bulkcmd);
 
Having a table of DBCC command names turn on the Results to text option in Management Studio run the following query:
 
SELECT 'DBCC HELP(''' + [dbcc] + ''') WITH NO_INFOMSGS'
FROM #dbccs
ORDER BY [dbcc];
 
All you have to do is to copy the generated DBCC HELP commands and execute them.
 
 
NOTE: You will probably have to remove the BOM (byte order mark) from the very first DBCC command name returned by the above query (you can notice some “trash” there). You can delete BOM before running BULK INSERT. The best way to eliminate BOM is to open the .rpt file (it is encoded in UTF-8) with Notepad++ editor and convert it to UTF-8 without BOM.
  
NOTE: The trace flag 2588 (or 2520 on SQL Server 2000) has to be enabled (on session or server level) to be make sure DBCC HELP returns a syntax for every DBCC command.
 

I’m attaching the script containing all the code from this post.

NOTE: Run any undocumented DBCC command at your own risk. Undocumented means unsupported so do not expect Microsoft (or me) to help you with any issue caused by running any unsupported piece of code on your SQL Server instances.

Attachments:

Undocumented DBCCs (T-SQL script)

DBCC commands on SQL Server 2008

DBCC commands on SQL Server 2005

DBCC commands on SQL Server 2000