MS SQL Server - SPID being destroyed

Author

SPID being destroyed - how?

stevep

2006-11-08, 7:12 pm

We have a client / server app that establishes a SQL connection via ODBC.
The app's database keeps track of the SQL SPID and its own client session id
in its own SESSIONTAB table. It uses it's session id internally rather than
the SQL SPID.

The server-side code of the app periodically checks that the SESSIONTAB
entries are valid by ensuring the SQL SPID is still alive on the server. If
not, it deletes that row from its SESSIONTAB table thus cleaning itself up.

Problem is that for some reason SQL Server is destroying the SPID and we are
losing client app connections. This does not seem to be related to any
timeouts or external events - we have seen it at various random times after
establishing the connection.

Under what circumstances does SQL Server 2000 (SP3a) kill off SPIDs ? I
presume it is either a cleanup task or a network 'glitch' that triggers it?
(network traces show TCP keepalive packets between the client and server up
to the point of failure)

How can I find out why this is happening? What kind of traces can I run?

BTW, there are no entries in the SQL error log nor the eventlogs. The first
we know about this has happened is when our clients fail.

Thanks.

John Bell

2006-11-10, 5:15 am

Hi

Have you looked at the Attention, Exception and OLEDB Error events in Errors
and Warnings Category? You don't say how you check that a SID has a valid
connection, if this query times out will you kill off the session
incorrectly? SQL Server itself will not kill off sessions that are active, do
you RAISERROR anywhere?

John

"stevep" wrote:

> We have a client / server app that establishes a SQL connection via ODBC.
> The app's database keeps track of the SQL SPID and its own client session id
> in its own SESSIONTAB table. It uses it's session id internally rather than
> the SQL SPID.
>
> The server-side code of the app periodically checks that the SESSIONTAB
> entries are valid by ensuring the SQL SPID is still alive on the server. If
> not, it deletes that row from its SESSIONTAB table thus cleaning itself up.
>
> Problem is that for some reason SQL Server is destroying the SPID and we are
> losing client app connections. This does not seem to be related to any
> timeouts or external events - we have seen it at various random times after
> establishing the connection.
>
> Under what circumstances does SQL Server 2000 (SP3a) kill off SPIDs ? I
> presume it is either a cleanup task or a network 'glitch' that triggers it?
> (network traces show TCP keepalive packets between the client and server up
> to the point of failure)
>
> How can I find out why this is happening? What kind of traces can I run?
>
> BTW, there are no entries in the SQL error log nor the eventlogs. The first
> we know about this has happened is when our clients fail.
>
> Thanks.

Sponsored Links