0

I need a way to know when a session closes in SQL Server so I can clean up any resources allocated for that session by running a stored procedure I have.

When things are running perfectly, the client app would call a Cleanup routine to do this. But if the user gets disconnected unexpectedly, I need something on the server to know ... and call the cleanup.

Thinking in terms of languages like VB or Delphi, I would register an event handler for this.

I see that SQL Server has 'Extended Events' but I have not figured out how to react to those events in a stored procedure or trigger...

Or is there some other way to capture a SessionClose and then call a stored procedure?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Thomas Oatman
  • 301
  • 2
  • 9
  • 1
    Does the cleanup have to run immediately? If not a scheduled cleanup, that clears up stuff once an hour, or daily might do the trick. – Mirronelli Mar 17 '21 at 17:21
  • Not a bad idea @Mirronelli. My resources are based on the SessionId. So my concern is to have things cleaned up before another process gets that same ID again. Otherwise, the existing StoredProcedure would associate the old data with this new process. – Thomas Oatman Mar 17 '21 at 17:44
  • Do you have the option of making your resource reliant on something other than session id? For example, before VB runs a task, it puts an entry in the DB with a GUID with a start date and NULL end date. Your resources could tie to that GUID. If something goes wrong and end date for that GUID is not populated by 24h, a process could clean up the resources. – zedfoxus Mar 17 '21 at 17:54
  • 1
    You are on to something @zedfoxus.    The Session ID is required.      But I can make a unique index on SessionID | GUID.      So if I see the same SessionID come in with a different guid, I can assume the old one is dead and run cleanup.      Thanks to both of you. – Thomas Oatman Mar 17 '21 at 18:25
  • Once you try out a solution like that, add your own answer below and then mark your own answer as accepted, if possible. That’ll give closure to your question and others with similar issue can find value from your answer. – zedfoxus Mar 17 '21 at 22:35

2 Answers2

1

Better yet !

From here : Detecting disconnection of the client in SQL Server

You can create an event notification for the Audit Logout event. The notification can launch an activated procedure. Consider though that event notifications are asynchronous.

Thomas Oatman
  • 301
  • 2
  • 9
0

I ended up solving this with an Init | Deinit type of function.

I am using Native-Complied Stored Procedures which are limited in what information they can obtain. So the Init works out well -- the client (or wrapper SP) can get the info needed, pass to Init, and it stores that off to a session table.

The client calls Init before it does anything else. If there are resources associated with this session, it is assumed the first user has died and the previous resources released.

Extremely simplified Pseudo code:

function Init(info . . .)
begin
     if ( isSessionActive(@@spid) )
          releaseSession(@@spid)
     newSession(@@spid)
end

function Deinit()
begin
     releaseSession(@@spid)
end

native-compiled procedure doSomething()
begin
     getResources(@@spid, . . . .)
end
Thomas Oatman
  • 301
  • 2
  • 9