'sleeping' SQL Server Processes | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

‘sleeping’ SQL Server Processes

Hi, I have a JSP-Servlet application using a type-4 Driver for SQL2000. The app. also extensively uses Stored Procedures. My problem is with ‘sleeping’ processes in SQL Server. When the app is running in a multi user environment and I do a sp_who2 on the database, I see a lot of SQL processes in ‘Sleeping’ status. Many such sleeping processes are from clients who have already disconnected from the application. Somehow, SQL is either not completing the process or freeing up this process. What could be the reason for so many ‘sleeping’ process. Also, many times, I get blocking processes. When I do a dbcc inputbuffer on these blocking process I get ‘sp_cursorclose,1’ or ‘sp_execute’ or ‘null’ in the results. What could be causing these blocking process ? Any feedback would be welcome and useful. Thanks

I am not familiar with your JSP driver, so I don’t know if my comments apply to it or not. One potential cause of this is the connection pooling that your driver may have implemented. When connection pooling is used, even if the user’s connection is dropped, the connection will remain open with the idea that another connection may be able to reuse it. Generally, with connection pooling, after a specified amount of time, any unused connections should automatically disconnect. Blocking processes can be caused by many things, generally because a transaction is running too long. You will need to identify which connection is causing the blocking, and then look at this connection carefully to see what it is doing that is taking so long, trying to find ways to speed it up.
Brad M. McGehee
And also after running SP_WHO to know the command executing runn DBCC INPUTBUFFER(spid). YOu need to use disconnect and reconnect method from the application which will assure the connection is closed once the transaction is complete at server end. Satya SKJ

Not sure if this is automatically cleared up but it might be worth seeing if all cursors are and deallocated (and closed obviously)