Printing SPID information along with DBCC output

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 he 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:

 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;

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, speak frequently at conferences, and write at SQLPerformance and MSSQLTips. In real life I am a father of two, an architect at Wayfair, and my pronouns are he/him.

8 Responses

  1. Pei Zhu says:

    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.

  2. dokter love says:

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

  3. AaronBertrand says:

    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).

  4. cinahcaM madA says:

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

  5. AaronBertrand says:

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

  6. cinahcaM madA says:

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

  7. AaronBertrand says:

    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).

  8. Ben Thul says:

    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.