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

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

The undocumented DBCC PSS command enables you to obtain low-level information about users and processes currently connected to a SQL Server.

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 query (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

  • Tip: Undocumented stored procedure: View SQL Server 2000 raw data
  • Tip: Use DBCC CLEANTABLE to reclaim unused space in SQL Server tables
  • Expert response: Fragmentation based on results from DBCC SHOWCONTIG


  • This was last published in March 2006

    Dig Deeper on SQL Server Stored Procedures

    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