October 6, 2011 | SQL Server

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;

8 comments on this post

    • Ben Thul - October 6, 2011, 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.

    • AaronBertrand - October 6, 2011, 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).

    • cinahcaM madA - October 7, 2011, 3:35 AM

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

    • AaronBertrand - October 7, 2011, 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 - October 7, 2011, 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 - October 7, 2011, 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).

    • dokter love - October 9, 2011, 8:07 PM

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

    • Pei Zhu - October 10, 2011, 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.

Comments are closed.