How do I kill a persistent locked database connection

[Cheadle]

Using SQL Server 7.0 (no SP):

We occasionally end up with a database connection listed in 'Current Activity' - 'Process Info' in Enterprise Manager that shows a status of 'rollback', but where the connection is not still in place as far as the client is concerned, i.e. the PC has been logged off the network and sometimes switched off, sometimes for 24 hours or more. SQL Server will not allow us to kill the process (killing it has no effect) which has records locked. We have had to stop and re-start SQL Server to fix this in the past. Is there a less drastic way of dealing with this problem?

 

[tlbroadbent]

We've encountered the same problem and have yet to discover a btter solution than restarting SQL Server. I'd appreciate knowing if anyone has found a better solution. Terry

http://members.home.net/tlbroadbent/index.htm

 

[Dilyias]

This is a bug in SQL Server 7, and the only solution is to stop and start the SQL service. Supposably this has been corrected in SP3. (and we haven't seen this problem since upgrading to SP3 so I'd assume it's a safe bet)

-Eric

 

[Dilyias]

Ooops, I forget to mention,

This rollback occurs when a long query uses both processers. One way to prevent it from happening (if you use SMP, and you do not wish to upgrade to SP3) is to tell the server to only use one at a time per query, in the properties of the server under parallelism chose 'use [1] processer(s)'