Among undocumented DBCC commands in SQL Server, DBCC RESOURCE is one of the few that can be used to report back on SQL Server's own internal settings rather than reports on detailed information about data structures within SQL Server (such as tables, databases, indexes, etc.)
The basic command syntax for DBCC RESOURCE is:
DBCC TRACEON (3604)
DBCC RESOURCE
That command produces a report divided into three sections labeled RESOURCE, PERFMON and DS_CONFIG.
RESOURCE lists many core settings for the server. MaxCPUs, for instance, indicates how many physical processors this installation of SQL Server can use directly. Some of the settings, like DefaultLogin, servicename and DefaultDomain, are pretty self-evident. The rflag1 setting is one of the most useful; it has a series of bitwise settings used to describe some basic SQL Server behaviors. If the rightmost bit is set, it means the server is running in single-user mode.
PERFMON lists various performance-monitor counters, most of which are only useful to people who might want to gather SQL Server performance information internally. If you are trying to get this information in a context where you don't have direct access to performance counters, such as in a stored procedure, this would be one way to obtain the data.
The DS_CONFIG variables describe many local configuration parameters, such as index fill factors, the default database size or tape retention time in days. Some of the stats listed here can be useful in internal contexts, as with PERFMON. For instance, you can use the cwritestatus indicator to determine whether the server crashed or not during the last attempt to update its own configuration data. If it's set to 0, the data was written successfully; if it's set to 1, the server crashed on the last configuration area update and the data is considered "dirty."
You'll find a very detailed breakdown of each of the parameters produced by DBCC RESOURCE at TransactSQL.com.
About the author: Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!
More information from SearchSQLServer.com
Undocumented DBCC: Command to view a database transaction log
Undocumented DBCC: Use PSS command to view user and process information
Undocumented stored procedure: View SQL Server 2000 raw data
