Printing SPID information along with DBCC output
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;