Viewing the output of any SQL Server SPID

When investigation problems whit SQL Server, we often use commands like DBCC INPUTBUFFER, or tools like SQL Server Profile to view the various commands that arrive to SQL Server, thereby finding useful information such as errors, duration, CPU usage, IO usage and so on.

But, what if you don’t want to see what query the client sent to SQL Server but rather the result that SQL Server sent back to the client? That is not as easy as using Profiler, but can be done. Sort of.

The command to use is DBCC OUTPUTBUFFER (SPID ,[otional request id]) . Full documentation of this command is at http://msdn.microsoft.com/en-us/library/ms189821.aspx

The drawback is that you can only see the curent outputbuffer, not a continous stream as with Profiler for input. But if you know the SPID of the problematic connection/application you can atleast see what the last results or messages returned to it looks like.

And you could create a little loop like this to collect info for a little bit of time, and the view it.

create table #tmp (output varchar(max))

declare @buf int=0

while @buf< 100

begin

insert into #tmp exec (‘dbcc outputbuffer (83)’)

select @buf = @buf +1

end

select * from #tmp

drop table #tmp