Detecting disconnection of the client in SQL Server

Question

When a client connection with the SQL Server (from client side) is cut, how can I detecting this disconnection in SQL Server (2008 or 2012)?

Can I solve this problem with Server Triggers?

score 3 · Accepted Answer · answered Jun 23 '12 at 11:48

3

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.

answered Jun 23 '12 at 11:48

Remus Rusanu

score 2 · Answer 2 · answered Jun 23 '12 at 10:35

2

You could query one of the sys tables (sysprocesses)

SELECT 
   DB_NAME(dbid) AS Database, 
   loginame AS LoginName
FROM  sys.sysprocesses

You can also run the following stored procedure to see who is active:

 sp_who2

You would have to have a SQL Job or an active agent checking to see who has dropped out.

answered Jun 23 '12 at 10:35

Darren

1

Database is a reserved word, you need to put brackets like: [Database]. Or else we get "Incorrect syntax near the keyword 'Database'". I tried to edit your post, but my edit was rejected. – Matt Roy Jan 16 '18 at 20:47
This does not work if you need to catch the 'event' before the user logs back in or the session ID is reused. – Thomas Oatman Apr 22 '21 at 14:25

2 Answers2