Capture and react to a Session Close event in SQL Server?

Question

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?

Answer 1

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

Answer 2

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.