6

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?

2 Answers 2

14

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.

0

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;
1
  • 1
    (1) I assumed you knew the login name, because your question title says for ONE user - this sounded like a single SQL login used in the app's connection string. (2) using start_time would also kill sessions that have 5-hour-long running queries, so that would be the difference. If you need to clear out the database in order to do something that takes exclusive locks, leaving those there won't help you. (3) this really should be a comment on my answer, not a different answer. Dec 16, 2015 at 20:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.