September 9, 2004 at 12:49 pm
Hi all, I have created users using SQL security. Is there a way to disconnect them from a database when there has been a period of no activity?
Thanks
Gary
September 10, 2004 at 3:28 am
Try something like this:
DECLARE
@timeout int
SELECT
@timeout = 300
DECLARE ACur CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
spid
FROM
sysprocesses
WHERE
DATEDIFF(s, last_batch, getdate()) > @Timeout
AND spid> 25
You also have to add filter for users nt_name, loginame (I think you do not want disconnect each user including system processes, long running statistics etc ).
Maybe filter CMD.
AND spid> 25 - not try to kill system processes. 25 - is empiric, I do not know why 25, and I can't find it in documentation. Look on your system, maybe you'll have to write 30, maybe 20 will be sufficient.
Then open cursor and KILL each spid.
Next task - shedule that.
Good luck!
September 10, 2004 at 3:43 am
maybe off track but: Is your app closing the connections gracefully?
If you are using ADO.Net you need to make sure you .Close() the connection before destroying the object, otherwise it can stay open. Stupid problem I know, but good programming practice in anycase.
Your app should only need to open a connection when it wants to do something, and should ideally close the connection as soon as it is finished. ADO connection pooling will look hold a connection for a short amount of time (30 seconds?) after closing in case it needs to be reused.
I've encountered a number of Apps that make hold open multiple database connections unnecessarily and have cause locking problems as a result.
Julian Kuiters
juliankuiters.id.au
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply