현재 연결된 SPID 마지막 작업들 보기

sp_who2 에 붙어있는 51번 이상 spid 들의 마지막 실행한 SQL 보기

-- 내 SPID 보기 select @@spid DROP TABLE #TEMP1 CREATE TABLE #TEMP1 ( Seq INT IDENTITY(1,1), spID INT ) -- 시스템에 할당된 SPID 제외 INSERT INTO #TEMP1 (spID) SELECT DISTINCT spID FROM sys.sysprocesses WHERE spID > 50 DECLARE @wMax INT DECLARE @wIndex INT DECLARE @wSPID INT DECLARE @handle VARBINARY(64) DECLARE @wText VARCHAR(8000) SELECT @wMax = 0, @wIndex = 1 SELECT @wMax = COUNT(*) FROM #TEMP1 WHILE(@wIndex <= @wMax) BEGIN SELECT @handle = sql_handle,@wSPID = spid from sys.sysprocesses where spid = ( SELECT spid FROM #TEMP1 WHERE Seq = @wIndex ) SELECT @wText = text FROM sys.dm_exec_sql_text(@handle) PRINT('---------------------------------------------------------') PRINT('') PRINT('spid:' + CAST(@wSPID AS VARCHAR)) PRINT('-----------') PRINT(@wText) PRINT('') PRINT('---------------------------------------------------------') SELECT @wIndex = @wIndex + 1 END

 

 

결과

--------------------------------------------------------- --------------------------------------------------------- spid:77 select @@spid; select SERVERPROPERTY('ProductLevel'); --------------------------------------------------------- --------------------------------------------------------- spid:78 SELECT number_sv, example_sv, no ,number_sv, title_sv, other, number_sv-1 AS cnt FROM table_name WHERE date_sv <= CONVERT(char(10), getDate(), 120) AND date_end >= CONVERT(char(10), getDate(), 120) ORDER BY no DESC --------------------------------------------------------- ---------------------------------------------------------