BwJcSqjg

1/*
2Returns details on all running SQL jobs for the current database on the SQL server.
3Also tries to show locked and awaiting locked records jobs are delaying each other.
4Can only show SP names and full direct SQL queries, not SP peramaters passed in. 
5These can only be seen in SQL Server Profiler.
6*/
7
8IF object_id('tempdb..#who2') IS NOT NULL
9BEGIN
10	DROP TABLE #who2
11END
12CREATE TABLE #who2 (
13	spid INT,
14	status VARCHAR(2000),
15	login VARCHAR(2000),
16	hostname VARCHAR(2000),
17	blkby VARCHAR(2000),
18	DBName VARCHAR(2000),
19	command VARCHAR(2000),
20	cputime INT,
21	diskio INT,
22	lastbatch VARCHAR(2000),
23	programname VARCHAR(2000),
24	spid2 INT,
25	requestedid INT
26)
27
28--Insert the contents of sp_who2 in to a temp table
29INSERT INTO #who2
30EXEC sp_who2
31
32--Remove any sp_who2 results that are not needed (show any unexpected ones as thse may be important).
33DELETE FROM #who2
34	WHERE status = 'sleeping'
35		OR status = 'BACKGROUND'
36
37IF object_id('tempdb..#inputbuffer') IS NOT NULL
38BEGIN
39	DROP TABLE #inputbuffer
40END
41CREATE TABLE #inputbuffer (
42	spidID INT IDENTITY(1,1) NOT NULL,
43	eventType VARCHAR(2000),
44	parameters INT,
45	eventInfo VARCHAR(MAX),
46)
47
48IF object_id('tempdb..#inputBufferSpidId') IS NOT NULL
49BEGIN
50	DROP TABLE #inputBufferSpidId
51END
52CREATE TABLE #inputBufferSpidId (
53	ID INT IDENTITY(1,1) NOT NULL,
54	spid INT
55)
56
57--Create the cursor based on the #who2 table
58DECLARE @spid INT;
59DECLARE @spidCommand VARCHAR(2000);
60DECLARE runnable_cursor CURSOR
61	FOR SELECT spid
62		FROM #who2 
63		ORDER BY cputime;
64
65--Loop through the results of the #who2 table and record the details to the tables
66OPEN runnable_cursor;
67FETCH NEXT FROM runnable_cursor
68INTO @spid;
69WHILE @@FETCH_STATUS = 0
70BEGIN
71	SET @spidCommand = 'dbcc inputbuffer(' + CAST(@spid AS VARCHAR(10)) + ');'
72	INSERT INTO #inputBuffer
73	EXEC(@spidCommand);
74	
75	INSERT INTO  #inputBufferSpidId (spid)
76	SELECT @spid
77	
78	FETCH NEXT FROM runnable_cursor
79	INTO @spid;
80END
81CLOSE runnable_cursor;
82DEALLOCATE runnable_cursor;
83
84--Remove any duplicate entries for a SPID from the #inputBufferSpidId table as these would cause odd end results.
85--Each duplicate of a SPID will have the same SQL query so for each SPID we can remove all OTHER instacnes of this SPID.
86DECLARE @currentDupRemoveID INT;
87SET @currentDupRemoveID = 1;
88DECLARE @maxDupRemoveID INT;
89SELECT @maxDupRemoveID = MAX(ID) FROM #inputBufferSpidId
90DECLARE @thisSpidDupRemoveID INT;
91DECLARE @thisSpidCountDupRemoveID INT;
92WHILE @currentDupRemoveID < @maxDupRemoveID
93BEGIN
94	SET @thisSpidDupRemoveID = ''
95	SELECT @thisSpidDupRemoveID = spid
96		FROM #inputBufferSpidId
97		WHERE ID = @currentDupRemoveID
98	IF(@thisSpidDupRemoveID IS NOT NULL)
99	BEGIN
100		DELETE FROM #inputBufferSpidId
101			WHERE SPID = @thisSpidDupRemoveID AND ID != @currentDupRemoveID
102	END
103	SET @currentDupRemoveID = @currentDupRemoveID + 1;
104END
105
106--Get the total results to display
107SELECT 'Current Running Transactions'
108SELECT ibsid.spid, w.status, W.login, W.hostname, W.DBName, W.command, W.cputime, W.diskio, W.lastbatch, W.programname, ib.eventType, ib.parameters, ib.eventInfo 
109	FROM #who2 as W
110		INNER JOIN #inputBufferSpidId as ibsid on ibsid.spid = W.spid
111		INNER JOIN #inputBuffer as ib ON ib.spidID = ibsid.ID
112	ORDER BY ibsid.spid
113GO
114
115--Create temporary tables to store the lock info in
116CREATE TABLE #currentLocks (
117	spid INT
118	,resourceType VARCHAR(200)
119	,tableName VARCHAR(200)
120	,lockedRows INT
121)
122
123--Get the current locks being caused by the current running transactions
124SELECT 'Current Locks by active Transactions - Experimental'
125INSERT INTO #currentLocks (spid, resourceType, tableName, lockedRows)
126SELECT tl.request_session_id, tl.resource_type, OBJECT_NAME(part.object_id) AS TableName, count(*) AS LockedRows 
127	FROM sys.dm_tran_locks AS tl
128		INNER JOIN sys.partitions AS part ON tl.resource_associated_entity_id = part.hobt_id
129	WHERE tl.resource_database_id = DB_ID()
130		AND tl.request_mode = 'X'
131		AND tl.request_session_id IN (
132			SELECT w.spid
133				FROM #who2 AS w
134		)
135	GROUP BY tl.request_session_id, tl.resource_type, part.object_id
136	ORDER BY tl.request_session_id, TableName
137SELECT * FROM #currentLocks
138GO
139
140--Get the current waiting table acess locks for the current running transactions
141SELECT 'Waiting/Upcomming Table Access for active Transactions - Experimental'
142SELECT wl.*
143	FROM (
144		SELECT tl.request_session_id, tl.resource_type, OBJECT_NAME(tl.resource_associated_entity_id) AS TableName 
145			FROM sys.dm_tran_locks AS tl
146			WHERE tl.resource_database_id = DB_ID()
147				AND tl.resource_type = 'OBJECT'
148				AND tl.request_mode = 'IX'
149				AND tl.request_session_id IN (
150					SELECT w.spid
151						FROM #who2 AS w
152				)
153			GROUP BY tl.request_session_id, tl.resource_type, tl.resource_associated_entity_id
154	) AS wl
155	WHERE NOT EXISTS (
156		SELECT TOP 1 cl.spid
157			FROM #currentLocks AS cl
158			WHERE (
159				(cl.spid = wl.request_session_id)
160				AND (cl.tableName = wl.TableName)
161			)
162	)
163	ORDER BY wl.request_session_id, wl.TableName
164
165--Tidy Up
166DROP TABLE #who2
167DROP TABLE #inputBuffer
168DROP TABLE #inputBufferSpidId
169DROP TABLE #currentLocks