Problem solve Get help with specific problems with your technologies, process and projects.

Undocumented DBCC: Report on settings with DBCC RESOURCE

DBCC RESOURCE allows you to report back specifically on SQL Server internal settings rather than data structure information.

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

  • This was last published in March 2006

    Dig Deeper on SQL Server Database Modeling and Design

    PRO+

    Content

    Find more PRO+ content and other member only offers, here.

    Start the conversation

    Send me notifications when other members comment.

    By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

    Please create a username to comment.

    -ADS BY GOOGLE

    SearchBusinessAnalytics

    SearchDataCenter

    SearchDataManagement

    SearchAWS

    SearchOracle

    SearchContentManagement

    SearchWindowsServer

    Close