Undocumented DBCC: Use PSS command to view user and process information

The DBCC PSS command enables you to obtain and dump out detailed information about processes and users currently connected to a SQL Server. It has remained an undocumented feature because the type of information dumped out by PSS has changed from one SQL Server edition to the next, and there's no guarantee it will continue to be supported. But it is still useful for obtaining low-level process data.

The syntax for DBCC:

DBCC TRACEON(3604)
DBCC PSS(<userID>, <processed>, <displayoptions>)

When you run PSS with nothing but user 0 as the parameter -- DBCC PSS(0) -- the database dumps out a list of all active users and their attendant processes. If you then rerun DBCC PSS with a user number and a process number, you'll see an extremely detailed dump of all of the user's flags as well as the execution context, the input stream and the output buffers for that process.

DBCC TRACEON(3604)
DBCC PSS(5,53) <these are arbitrary user and process numbers, but you get the idea>

The <displayoptions> option is not required, but if you set it to 1 -- e.g., DBCC PSS(5,53,1) -- you'll see all open descriptors and the sequence tree for the que

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Database Administration Top load balancing methods for SQL Server Performance implications of transaction log autogrowth in SQL Server The keys to database backup protection for SQL Server Understanding transparent data encryption in SQL Server 2008 Working with sparse columns in SQL Server 2008 Determining the source of full transaction logs in SQL Server Implementing SQL Server 2008 FILESTREAM functionality Improving SQL Server full-text search performance Using the OPENROWSET function in SQL Server New replication features in SQL Server 2008 and what they mean to you SQL Server Stored Procedures Top 10 SQL Server development tips of 2008 SQL Server trigger vs. stored procedure to receive data notification SQL Server errors, failures and other problems fixed from the trenches SQL Server and data manipulation in T-SQL How to use SQL Server 2008 hierarchyid data type SQL Server stored procedures tutorial: Write, tune and get examples Check SQL Server database and log file size with this stored procedure SQL Server source code analysis and management adds database security Configure SQL Server Service Broker for sending stored procedure data Find size of SQL Server tables and other objects with stored procedure

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary library  (SearchSQLServer.com) trigger  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

ry (if there is one) also dumped out to the console. The output buffers are raw data, so if they feature things like column names or other internal data they're usually dumped in UTF-16 format (the codepage used by SQL Server internally).

If you simply dump a user ID (except user 0) without a process ID, you'll see data for all the processes currently executing under that ID. Keep in mind this might take a while to print if you have a lot of processes currently running. Most flags dumped by PSS are not documented, but some are fairly self-evident: Language is the language ID for the connection and prowcount is the number of rows returned as set by the ROWCOUNT option for that connection.

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