1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
|
DECLARE @ACTIVE BIT; SET @ACTIVE = 1;
DECLARE @WITH_SQL BIT; SET @WITH_SQL = 1;
DECLARE @LIST_PROCESSES TABLE
(
SPID INT,
Hostname SYSNAME,
Status SYSNAME,
Command SYSNAME,
[CPU Time] INT,
[Disk I/O] INT,
Blocked INT,
[Database] SYSNAME,
[Login] SYSNAME,
[Last batch] DATETIME,
Program SYSNAME
);
INSERT @LIST_PROCESSES
SELECT
SPID,
HOSTNAME,
UPPER(STATUS),
UPPER(CMD),
CPU,
PHYSICAL_IO,
BLOCKED,
DB_NAME(DBID),
CONVERT(SYSNAME, RTRIM(LOGINAME)) AS LOGINNAME,
LAST_BATCH,
PROGRAM_NAME
FROM MASTER.DBO.SYSPROCESSES WITH (NOLOCK)
WHERE SPID > 50
AND SPID != @@SPID;
IF @ACTIVE = 1
DELETE @LIST_PROCESSES
WHERE STATUS = 'SLEEPING'
AND COMMAND = 'AWAITING COMMAND'
AND BLOCKED = 0
IF @WITH_SQL = 1
BEGIN
DECLARE @SQL VARCHAR(64);
DECLARE @PROCESSES TABLE
(
ID INT IDENTITY,
SPID INT,
Hostname SYSNAME,
Status SYSNAME,
Command SYSNAME,
[CPU Time] INT,
[Disk I/O] INT,
Blocked INT,
[Database] SYSNAME,
[Login] SYSNAME,
[Last batch] DATETIME,
Program SYSNAME,
SQL VARCHAR(256)
);
INSERT @PROCESSES
SELECT *, NULL -- SQL
FROM @LIST_PROCESSES;
DECLARE @I INT; SET @I = 1;
DECLARE @HISTO_SPID INT; SET @HISTO_SPID = 1;
DECLARE @NB_PROCESSES INT; SELECT @NB_PROCESSES = COUNT(*) FROM @PROCESSES;
SET NOCOUNT ON;
WHILE @I <= @NB_PROCESSES
BEGIN
DECLARE @SP_ID INT;
SELECT @SP_ID = SPID
FROM @PROCESSES
WHERE ID = @I;
IF @HISTO_SPID != @SP_ID
BEGIN;
DECLARE @DBCC_INPUT_BUFFER TABLE
(
EventType SYSNAME,
Parameters INT,
EventInfo TEXT
);
SET @SQL = 'DBCC INPUTBUFFER (' + CONVERT(VARCHAR, @SP_ID) + ') WITH NO_INFOMSGS';
SELECT @SQL;
INSERT @DBCC_INPUT_BUFFER
EXEC (@SQL);
BEGIN TRY
UPDATE @PROCESSES
SET SQL = (SELECT SUBSTRING(Eventinfo, 1, 256) FROM @DBCC_INPUT_BUFFER)
WHERE SPID = @SP_ID
END TRY
BEGIN CATCH
SELECT @SP_ID, SUBSTRING(Eventinfo, 1, 256) FROM @DBCC_INPUT_BUFFER;
END CATCH;
SET @HISTO_SPID = @SP_ID;
END;
SET @I = @I + 1;
END;
SET NOCOUNT OFF;
END;
SELECT *
FROM @PROCESSES |
