DBCC OutputBuffer

Being day 22 of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC OUTPUTBUFFER.

I missed a few days on the DBCC Commands due to attending SQL Saturday in Redmond, and the release of my book on Amazon.com.  I am now back on track to finish out the rest of the month with more DBCC commands.

Description:

If you remember DBCC InputBuffer from last week, DBCC OutputBuffer has a very similar syntax.  Rather than seeing what was input, we see what was returned by the server. The output memory buffer contains the data in both hexadecimal and ASCII output.

The parameter is the session_id or the request_id.

DBCC OUTPUTBUFFER Syntax:

dbcc outputbuffer
(
    session_id [ , request_id ]
)
    [ WITH NO_INFOMSGS ]

Example:

The following example first will let us find a specific session, from that session id (SPID) we will look up what the input was as well as what the output returned is.

First, find a session.

SELECT *
 FROM master.dbo.sysprocesses P
 ORDER BY last_batch DESC;

From here we can see that there is a SPID or session ID associated with a backup command is SPID 63.

Next we can take a look at the input buffer and the output buffer associated with the SPID 63.

DECLARE @spid as INTEGER = 63;

DBCC InputBuffer(@spid);

DBCC OutputBuffer(@spid);

From the output we see 2 panels, the first one shows the command that was issued on SPID 63, and the second one shows us a hex dump of what was returned in the output buffer from SPID 63.  Keep in mind that the result set from the DBCC OutputBuffer command is always 256 lines, but the results from the last command may not fill the entire 256 rows, instead it may only be the beginning of the outout buffer.

For instance since the SPID we are looking at is associated with a backup command we can see that the backup is reporting 40 percent processed. The extra spaces are part of the NVARCHAR type or the extra space used to represent unicode characters.

Notes:

For more information see TSQL Wiki DBCC outputbuffer.

DBCC Command month at SteveStedman.com is almost as much fun as closing your facebook account.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!