December 30, 2009 at 4:09 pm
The app I am supporting counts licenses based on the number of simultaneous connections. Sometimes users forget to log off the program and their dormant
connections take necessary licenses preventing other users from logging in. Is there a way to automate killing sleeping processes on the SQL server side? Perhaps based on elapsed time since it was sleeping for a while? If somebody knows anything about it and can just point me in the right direction that would great.
Many thanks!
D
December 30, 2009 at 4:23 pm
3rd party app counts number of SQL connections to a specific database and uses that count as the control for it's licensing methodology. Is that correct?
There are scripts available that help to automate such a task as killing connections that are dormant. I would be cautious however in doing something like that unless you know for certain that the SPID involved is not actually doing something.
Try a few of the scripts readily available, and then use the one that suits your needs the best.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 30, 2009 at 4:25 pm
One such script can be found at http://blog.sqlauthority.com/2006/12/01/sql-server-cursor-to-kill-all-process-in-database/
I think it would need some mods to keep it to killing only the desired SPIDS
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 30, 2009 at 5:32 pm
Using the last_batch column in sys.sysprocesses is one way to go here, looking for any connections that have not executed SQL for a time that you would consider means the user has stopped working without logging off.
Example code (not fully tested)
print 'The following spids have a last active time older than 30 minutes and will be killed'
print ' '
-- 30 mins is an arbitrary value, adjust to your needs
declare @program_name char(30),
@userid char(10),
@spid smallint,
@last_batch datetime,
@date datetime,
@no_of_mins smallint,
@occurred char(1),
@statement nvarchar (10)
select @date = getdate()
set @occurred = 'N'
declare kill_cursor cursor for
select spid, convert(char(10),nt_username), convert(char(30), program_name), last_batch
from sysprocesses
where spid > 50
open kill_cursor
fetch next from kill_cursor
into @spid, @userid, @program_name, @last_batch
while @@fetch_status = 0
begin
select @no_of_mins = datediff(mi, @last_batch, @date)
if @no_of_mins > 29
begin
print convert(char(3),@spid) +' ' +@userid
set @statement = N'kill ' + convert(nvarchar(3),@spid)
exec sp_executesql @statement
set @occurred = 'Y'
end
fetch next from kill_cursor
into @spid, @userid, @program_name, @last_batch
end
close kill_cursor
deallocate kill_cursor
if @occurred = 'N'
begin
print 'none were found!'
end
This is not without risk, you risk killing connections that are active or the user intends to come back to, so you need to use your knowledge of the app to help determine truly idle connections. Other columns in sys.sysprocesses that might help here are program_name, hostname,nt_username and loginame (to narrow it down to connections from the app) or status or open_tran (value > 0 means connection has a transaction open)
---------------------------------------------------------------------
December 30, 2009 at 5:55 pm
why not have the app itself test for inactivity, and log people out after say, 20 minutes? then you could have a pop-up screen that said they were logged out. otherwise, when they come back to teh app, it might crash or otherwise behave erratically, giving false errors and stuff.
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply