Kill all sessions for ONE user in MS SQL

Question

So we have a bad external application that connects to the database, executes a stored procedure and gets an answer.

It never releases the database connection.

I need a script that either kills all of the sessions for one user or I need a script that kills all of the sessions that have been inactive for x hours for a particular database

Anyone have something like this?

Answer 1

There's not really a reliable way to pin sessions to a specific database (sysprocesses has this, but it relies on an active query's database context, and dm_exec_sessions has authenticating_database_id, but this relies on the connection string, login's default database, etc).

That said, this seems to meet all of your other requirements, attempting to kill all sessions using app_name, connecting as login_name, and who haven't issued a request in the last 5 hours:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'KILL ' + CONVERT(VARCHAR(11), session_id) + N';'
FROM sys.dm_exec_sessions
  WHERE [program_name] = N'app_name'
  AND login_name = N'login_name'
  AND last_request_start_time < DATEADD(HOUR, -5, SYSDATETIME());

EXEC sys.sp_executesql @sql;

If all the app does is call that one stored procedure and never does anything else at all, you could perhaps look at DBCC INPUTBUFFER for each spid, but that becomes very tedious very quickly.

Answer 2

Aaron, thank you - that led me to come up with a slightly modified solution.

I commented out the login_name because I won't know it at execution time. Put in the app_name because that is constant. And changed last_request_start_time to last_request_end_time (just in case).

Now off to package it up so I can run it as a maintenance job.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'KILL ' + CONVERT(VARCHAR(11), session_id) + N';'
FROM sys.dm_exec_sessions
  WHERE [program_name] = N'program_name'
  --AND login_name = N'login_name'
  AND last_request_end_time < DATEADD(HOUR, -5, SYSDATETIME());

EXEC sys.sp_executesql @sql;