November 12, 2002 at 3:33 pm
I was trying to restore a database but couldn't because "it was in use". I ran sp_who and found two accounts, both "sleeping". I killed them and restored the database without further problems.
My quesrion: if I kill a "sleeping" connection is it possible that data is lost by that user? (I'm amazed that a "sleeping" connection can derail the restore process. I also hope that sleeping connections time out!)
November 12, 2002 at 3:40 pm
A "Sleeping" process is a process waiting for a lock or user input. Know I suppose if a process started a transaction, did some stuff, then did some more stuff that had to wait for a lock. While it was waiting for a lock you killed the process, then the transaction would eventually roll back. Therefore some work would be lost.
Of course we would hope most application don't wait on user input in the middle of a transaction.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 12, 2002 at 7:28 pm
Think of a sleeping process as simply an idle one. It's not unusual to see them if users leave applications open (especially the SQL Server client tools). Unless the user chooses to terminate the connection, it typically remains up. Likewise, if a user is running an application like Query Analyzer which maintains a connection, it'll keep the connection unless the user closes the application, an interruption in network services occurs, or you stop the SQL Server service (to include a server reboot).
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
November 13, 2002 at 4:01 am
Generally a sleeping process is doing nothing. Client side thou they could have the data pulled and working with it and until they apply thru the client interface a need to change or query the server then you are not having any current impact. Of course if the restore replaces data they had on the client app and they requery then they will not get the same data, but their connection is dead and a good programmer shoul have taken that into account to make them aware.
November 13, 2002 at 4:37 am
Check openTrans in sysprocesses, if the value is >0 they have open transactions and killing them will result in a rollback of their transaction, and thus possible loss of data. No app should be sleeping and have transactions open .
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply