Hi all,

I want a trigger to get fired when my application's connection to the
database gets disconnected.

Currently I am able to find out using "sp_who", which servers are connected
to the database and their connection status.

I plan to monitor the Server Name and its status in the " sp_who "
procedure and created one job which will execute stored procedure and
perform some steps.

Is there any other way to execute a stored procedure and perform some steps
when a connection is lost to a database?

Can you please tell me any event which get triggered by sqlserver when
database connecteion lost and how to trap that event?

Any comments/suggestions would be appreciated.

Thanks..
Shilpa

Shilpa,

I know of no way to capture a dropped connection and the time of the drop
and execute some code. In fact, it is quite possible that SQL Server does
not know that a connection has been lost.

This is reflected in SQL Server 2005 implementing a Login Trigger, but not a
Logout (or whatever) Trigger.

One comment on your approach is to remember that a login can drop a
connection (through logging out or through some other problem) thus freeing
a SPID, then log back in and reclaim the same SPID. This happens fairly
often on a busy system. I don't know if that case would matter to you.

RLF

"Shilpa Uttarwar" <shilpa.uttarwar (AT) edisphere (DOT) com> wrote

Shilpa,

I know of no way to capture a dropped connection and the time of the drop
and execute some code. In fact, it is quite possible that SQL Server does
not know that a connection has been lost.

This is reflected in SQL Server 2005 implementing a Login Trigger, but not a
Logout (or whatever) Trigger.

One comment on your approach is to remember that a login can drop a
connection (through logging out or through some other problem) thus freeing
a SPID, then log back in and reclaim the same SPID. This happens fairly
often on a busy system. I don't know if that case would matter to you.

RLF

"Shilpa Uttarwar" <shilpa.uttarwar (AT) edisphere (DOT) com> wrote

Shilpa,

I know of no way to capture a dropped connection and the time of the drop
and execute some code. In fact, it is quite possible that SQL Server does
not know that a connection has been lost.

This is reflected in SQL Server 2005 implementing a Login Trigger, but not a
Logout (or whatever) Trigger.

One comment on your approach is to remember that a login can drop a
connection (through logging out or through some other problem) thus freeing
a SPID, then log back in and reclaim the same SPID. This happens fairly
often on a busy system. I don't know if that case would matter to you.

RLF

"Shilpa Uttarwar" <shilpa.uttarwar (AT) edisphere (DOT) com> wrote

Shilpa,

I know of no way to capture a dropped connection and the time of the drop
and execute some code. In fact, it is quite possible that SQL Server does
not know that a connection has been lost.

This is reflected in SQL Server 2005 implementing a Login Trigger, but not a
Logout (or whatever) Trigger.

One comment on your approach is to remember that a login can drop a
connection (through logging out or through some other problem) thus freeing
a SPID, then log back in and reclaim the same SPID. This happens fairly
often on a busy system. I don't know if that case would matter to you.

RLF

"Shilpa Uttarwar" <shilpa.uttarwar (AT) edisphere (DOT) com> wrote

Shilpa,

I know of no way to capture a dropped connection and the time of the drop
and execute some code. In fact, it is quite possible that SQL Server does
not know that a connection has been lost.

This is reflected in SQL Server 2005 implementing a Login Trigger, but not a
Logout (or whatever) Trigger.

One comment on your approach is to remember that a login can drop a
connection (through logging out or through some other problem) thus freeing
a SPID, then log back in and reclaim the same SPID. This happens fairly
often on a busy system. I don't know if that case would matter to you.

RLF

"Shilpa Uttarwar" <shilpa.uttarwar (AT) edisphere (DOT) com> wrote

Shilpa,

I know of no way to capture a dropped connection and the time of the drop
and execute some code. In fact, it is quite possible that SQL Server does
not know that a connection has been lost.

This is reflected in SQL Server 2005 implementing a Login Trigger, but not a
Logout (or whatever) Trigger.

One comment on your approach is to remember that a login can drop a
connection (through logging out or through some other problem) thus freeing
a SPID, then log back in and reclaim the same SPID. This happens fairly
often on a busy system. I don't know if that case would matter to you.

RLF

"Shilpa Uttarwar" <shilpa.uttarwar (AT) edisphere (DOT) com> wrote

Shilpa,

I know of no way to capture a dropped connection and the time of the drop
and execute some code. In fact, it is quite possible that SQL Server does
not know that a connection has been lost.

This is reflected in SQL Server 2005 implementing a Login Trigger, but not a
Logout (or whatever) Trigger.

One comment on your approach is to remember that a login can drop a
connection (through logging out or through some other problem) thus freeing
a SPID, then log back in and reclaim the same SPID. This happens fairly
often on a busy system. I don't know if that case would matter to you.

RLF

"Shilpa Uttarwar" <shilpa.uttarwar (AT) edisphere (DOT) com> wrote

Shilpa,

I know of no way to capture a dropped connection and the time of the drop
and execute some code. In fact, it is quite possible that SQL Server does
not know that a connection has been lost.

This is reflected in SQL Server 2005 implementing a Login Trigger, but not a
Logout (or whatever) Trigger.

One comment on your approach is to remember that a login can drop a
connection (through logging out or through some other problem) thus freeing
a SPID, then log back in and reclaim the same SPID. This happens fairly
often on a busy system. I don't know if that case would matter to you.

RLF

"Shilpa Uttarwar" <shilpa.uttarwar (AT) edisphere (DOT) com> wrote

Shilpa,

I know of no way to capture a dropped connection and the time of the drop
and execute some code. In fact, it is quite possible that SQL Server does
not know that a connection has been lost.

This is reflected in SQL Server 2005 implementing a Login Trigger, but not a
Logout (or whatever) Trigger.

One comment on your approach is to remember that a login can drop a
connection (through logging out or through some other problem) thus freeing
a SPID, then log back in and reclaim the same SPID. This happens fairly
often on a busy system. I don't know if that case would matter to you.

RLF

"Shilpa Uttarwar" <shilpa.uttarwar (AT) edisphere (DOT) com> wrote