Orphaned Sessions

In one of our previous blog posts, we had covered Dead Client Detection in Oracle. The same applies to SQL Server on Windows as well. Suppose the client application makes a connection and starts a transaction and holds locks and just then, the network connectivity is lost or a power-failure occurs on the machine from where the client application is running from (assuming that it is not running on the DB Server), in all such scenarios, the client is not able to communicate to the DB that the connection is going away and hence leaves hung sessions and if those sessions were holding locks, it will result into open locks on the DB and consumption of resources on the server. Such sessions are called as orphaned sessions. If the client application terminates in a clean fashion i.e. exits gracefully, then Windows closes the connection and notifies SQL Server. Same is true for those client applications that crash or are forcibly killed by the end user (using Task Manager or from the command line). These result into a dead connection and since Windows won’t know that the connection is dead, it will continue to report to SQL Server that it is an active connection and SQL Server will thus keep the session open on the server side and wait for the client to issue a command.

So, why are orphaned sessions a cause of concern? These sessions might be using resources on the server side like locks, temp tables, any open cursors etc. i.e. the work that that server session was doing before it became an orphaned session. These can starve out other sessions or block other sessions (in case of locks). This can then start leading to lock time-outs for other sessions.

And how can one identify these orphaned sessions and what can be done about them? It would have been good to have something like a DCD configuration option like Oracle has. In SQL Server, in sysprocesses table, a spid value of –2 is an indicator of connectionless, or orphaned transactions. You can look for scripts on this blog to help identify the existing locks in the system and see whether they have occurred due to the orphaned sessions in the system. Depending upon the application, there are two things that you may notice:

1) The spid value of –2. In this case you will have to use Kill UOW to kill the process. UOW is the Unit of Work of the DTC transaction and can be obtained from the syslockinfo table. You can also use the KILL spid/UOW with statusonly to check the status of the kill statement.

2) In case your application makes use of the ODBC API and uses server side cursors, then you will notice that the last statement that was being executed by the process is sp_cursorunprepare or sp_unprepare which are API cursors. The situation in this case could be that the application forgot to clean up the session in the case of an error condition and this thus resulted into a hung application.