SQL Server has an undocumented stored procedure sp_who2 that provides information about the connections to the database.
Recently I have been using sp_who2 to track down connections that are "suspended" which in my case means that our MS-Access application is not processing all of the requested data in a timely manner. This can interfere with other users, even though there is no actual blocking going on. Another issue that we're seeing is that users will log-in to their terminal server environment, launch the application & then leave it open for days at a time. Even though the connections are sleeping, they still require some resources on the SQL server.
The task at-hand is 1) determine which connections (SPID's) are suspended & what SQL was running, and 2) determine which connections have been sleeping for more than a day. The first item will reveal the SQL so we can find an alternate solution; the second will allow us to identify the sleepers so we can close them out.
I started by creating a temp table & inserting the sp output, and then running some code to look up the SQL text. . .
DECLARE
-- set this to the name of your database
@database sysname = 'my_database',
@not_sleeping int,
@not_today int,
@spid int,
@sqltext varbinary(128),
@sql_command nvarchar(4000),
@today nvarchar(50)
-- LastBatch only provides month & day
SELECT @today = LEFT(CONVERT(nvarchar,GETDATE(),101),5)
-- create temp table to receive sp output, and execute
IF OBJECT_ID(N'tempdb..#sp_who2', N'U') IS NOT NULL
DROP TABLE #sp_who2
SET NOCOUNT ON
CREATE TABLE #sp_who2
(
SPID int,
Status nvarchar(1000) NULL,
Login sysname NULL,
HostName sysname NULL,
BlkBy sysname NULL,
DBName sysname NULL,
Command nvarchar(1000) NULL,
CPUTime int NULL,
DiskIO int NULL,
LastBatch nvarchar(1000) NULL,
ProgramName nvarchar(1000) NULL,
SPID2 int,
REQUESTID int NULL
)
INSERT #sp_who2
EXEC sp_who2
-- look up the sql text for each SPID
ALTER TABLE #sp_who2 ADD sql_command nvarchar(4000)
-----------------------------------------------------------
WHILE EXISTS (SELECT 1 FROM #sp_who2 WHERE sql_command IS NULL)
BEGIN
SELECT @spid = spid FROM #sp_who2 WHERE sql_command IS NULL
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = @spid
SELECT @sql_command = TEXT
FROM sys.dm_exec_sql_text(@sqltext)
UPDATE #sp_who2
SET sql_command = @sql_command
WHERE spid = @spid
END
-----------------------------------------------------------
SELECT @not_sleeping = COUNT(*)
FROM #sp_who2
WHERE DBName = @database
AND status <> 'sleeping'
SELECT @not_today = COUNT(*)
FROM #sp_who2
WHERE DBName = @database
AND LEFT(LastBatch,5) <> @today
-- display a list of all SPID's of interest
IF @not_sleeping <> 0 OR @not_today <> 0
BEGIN
SELECT
SPID,
Status,
Login,
HostName,
BlkBy,
DBName,
Command,
CPUTime,
DiskIO,
LastBatch,
ProgramName,
SPID2,
REQUESTID,
sql_command
FROM
#sp_who2
WHERE
DBName = @database
AND (
Status <> 'sleeping'
OR
LEFT(LastBatch,5) <> @today
)
END
---------------------------------------
DROP TABLE #sp_who2
