Printing SPID information along with DBCC output

SQL Server

October 6^th, 20118

Printing SPID information along with DBCC output

October 6^th, 20118

There is a much better way to do this in SQL Server 2016 and better.

Today on the #sqlhelp hash tag on twitter, @sqlsponge asked:

I am trying to loop through each active spid for dbcc inputbuffer. How can I print spid with inputbuffer output?

My response was that they should use sp_WhoIsActive, or at least query the DMVs/DMFs manually, since they're much easier to work with than DBCC. But there is, of course, a way to do what he's asking if, for some reason, he's tied to DBCC (and on a version of SQL Server older than 2016):

CREATE TABLE #spids
(
   i    INT,
   spid INT
);
 
CREATE TABLE #dbcc
(
   i          INT IDENTITY(1,1), 
   EventType  NVARCHAR(255), 
   Parameters NVARCHAR(4000), 
   EventInfo  NVARCHAR(4000)
);
 
DECLARE @spid INT, @sql NVARCHAR(255);
 
SET @sql = N'DBCC INPUTBUFFER(@spid);';
 
DECLARE c CURSOR
   LOCAL STATIC FORWARD_ONLY READ_ONLY 
   FOR 
     -- substitute your own query to determine SPIDs to check:
     SELECT session_id 
       FROM sys.dm_exec_sessions 
       WHERE is_user_process = 1;
 
OPEN c;
 
FETCH NEXT FROM c INTO @spid;
 
WHILE @@FETCH_STATUS = 0
BEGIN
   INSERT #dbcc(EventType, [Parameters], EventInfo)
       EXEC sp_executesql @sql, N'@spid INT', @spid;
 
   INSERT #spids SELECT SCOPE_IDENTITY(), @spid;
 
   FETCH NEXT FROM c INTO @spid;
END
 
CLOSE c;
DEALLOCATE c;
 
SELECT s.spid, d.EventInfo
   FROM #spids AS s 
   INNER JOIN #dbcc AS d 
   ON s.i = d.i
   ORDER BY s.spid;
 
DROP TABLE #spids, #dbcc;

By: Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a husband, a father of two, a huge hockey and football fan, and my pronouns are he/him.

Pei Zhu says:

October 10, 2011 at 8:49 PM

I think if someone reminds Aaron with sys.dm_exec_sql_text thing, it is kind of insulting. There is situation I like to use dbcc inputbuffer for instance when I am interested in the sproc name i/o the underlying sql statements.
dokter love says:

October 9, 2011 at 8:07 PM

And just what Ben says to me is the sys.dm_exec_sql_text empty.En now?
AaronBertrand says:

October 7, 2011 at 5:08 PM

Right, neither do I. The point of the post was to show how to do it if you were tied to calling DBCC INPUTBUFFER manually, for whatever reason. I point folks to sp_whoIsActive all the time (in fact that was my first reply to @sqlsponge on twitter).
cinahcaM madA says:

October 7, 2011 at 5:00 PM

I don't know anything about his situation. Just pointing that out in case you didn't know it was in there.
AaronBertrand says:

October 7, 2011 at 4:28 PM

Well of course, but if @sqlsponge is on SQL Server 2000, or he can't create stored procedures in master…
cinahcaM madA says:

October 7, 2011 at 3:35 AM

EXEC sp_whoisactive @get_outer_command = 1 –returns DBCC INPUTBUFFER data 🙂
AaronBertrand says:

October 6, 2011 at 10:04 PM

Thanks Ben, those queries are out there all over the place, I assumed there was a reason he was using DBCC INPUTBUFFER. One I've noticed is that in some cases sys.dm_exec_sql_text is empty while DBCC does show data. This was back in 2005 so I'm not sure it's still possible today, but he may very well be on 2005 (or 2000 even – in which case the only thing that needs to change is the query to get the SPIDs in the first place).
Ben Thul says:

October 6, 2011 at 9:42 PM

If they have access to sys.dm_exec_sessions, they have access to sys.dm_exec_sql_text. A simple cross apply between the two and blamo… you've got your SQL text.