Of idle users and non-set based solutions

Like many DBAs I support some interesting applications that lead to interesting solutions. One, for example, is a global application where a user many time zones away might lose their connection. The application, in its infinite wisdom, leaves a session open in the database because just maybe that user will pickup where they left off, 10 hours ago. We all deal with constraints and one of my constraints here is licenses. The application has a set number of concurrent licenses and once we hit that limit, the error messages (along with emails and phone calls) start coming in.

How do I deal with this? With the the KILL command and, for the time being, a non-set based solution using (shutter) a cursor.

The procedure starts off innocently enough by populating a temp table with the results of sp_who2

CREATE TABLE #temp_sp_who2
([SPID] NVARCHAR(100),
[Status] NVARCHAR(100),
[Login] NVARCHAR(100),
[HostName] NVARCHAR(100),
[BlkBy] NVARCHAR(100),
[DBName] NVARCHAR(100),
[Command] NVARCHAR(100),
[CPUTime] NVARCHAR(100),
[DiskIO] NVARCHAR(100),
[LastBatch] NVARCHAR(100),
[ProgramName] NVARCHAR(100),
[SPID2] NVARCHAR(100),
[REQUESTID] NVARCHAR(100))

--Insert all active sessions into temp table
INSERT INTO #temp_sp_who2 (SPID, Status, Login, HostName, BlkBy, DBName, Command, CPUTime, DiskIO, LastBatch, ProgramName, SPID2, REQUESTID) EXEC sp_who2

Those results are filtered using the function dbo.MinutesOld (definition at the end of the post) to convert the text value of sp_who2 LastBatch column to date format by adding a year on the end, then using DATEDIFF with GETDATE() to find the number of minutes the session has been idle. It’s set at 120 minutes, a full 2 hours of idle time.


CREATE TABLE #temp_script (script NVARCHAR(MAX), scriptlog NVARCHAR(MAX))

--Insert old sessions into temp table
INSERT INTO #temp_script (script, scriptlog)
(
SELECT 'KILL ' + REPLACE(SPID,' ','') + ''
,'INSERT INTO ScriptLog (Category, Script, ScriptOwner) VALUES(''Idle User'',''KILL ' + REPLACE(SPID,' ','') + ' /* ' + Login + ' ' + CAST(dbo.MinutesOld(LastBatch) AS VARCHAR(4)) + ' */'',''Automated DBA'')' AS Script
FROM #temp_sp_who2
WHERE Login NOT LIKE ''
AND ProgramName IN ('')
AND dbo.MinutesOld(LastBatch) > 120)

After I find the idle users we get to the icky part of the procedure, the cursor. I know cursors are not the most elegant and efficient solution and I should always be looking for a set based solution. That being said, this procedure works and will not have large record sets to deal with. If a large record set is used someday there will be many other problems outside of this query. That also being said, I am looking to get rid of cursors and hopefully will be refactoring this in the near future.

The cursor is based on the filtered sp_who2 set which now contains only sessions that have been idle for over 2 hours. This filtered temp table has a column with a KILL command and a column with an INSERT statement to record the nefarious deed.


DECLARE @sqlScript AS NVARCHAR(MAX)
DECLARE @sqlInsertScript AS NVARCHAR(MAX)

--Execute and record kill command
DECLARE script_cursor CURSOR FOR SELECT script, scriptlog FROM #temp_script
OPEN script_cursor
FETCH NEXT FROM script_cursor INTO @sqlscript, @sqlInsertScript
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE sp_executesql @sqlscript
EXECUTE sp_executesql @sqlInsertScript
FETCH NEXT FROM script_cursor INTO @sqlscript, @sqlInsertScript
END

CLOSE script_cursor
DEALLOCATE script_cursor

--Clean up
DROP TABLE #temp_sp_who2
DROP TABLE #temp_script

This procedure is running regularly, booting out idle users and keeping us away from the license cap. It’s cheaper than buying more licenses

MinutesOld function

CREATE FUNCTION [dbo].[MinutesOld]
(@LastBatch VARCHAR(50))
RETURNS INT
AS
BEGIN
RETURN (DATEDIFF(MI, (REPLACE(@LastBatch,' ','/' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) + ' ')), GETDATE()))
END
GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s