Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
beegirl38
Starting Member
4 Posts |
 Posted - 2007-11-21 : 10:40:55
|
| SQL Server is maintaining Process threads for long-gone user sessions(Current activity -> Process). The hanging processes usually show "sp_releaseapplock_1" or Select convert(int, @@spid) as sid. What is triggering this and how can it be prevented? |
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-11-21 : 12:31:44
|
| I've seen 'connections/transactions leaks' is some applications, but before we start this, some questions:1. What do you call a 'thread'? spids (connections)? (they are different from internal SQL server threads)2. "sp_releaseapplock_1" or Select convert(int, @@spid) as sid are the last commands, executed in these connections?3. Is it just hanging or is it locking other processes? What is tran_count for this connections?4. May be you application is using connection pooling, and it is a normal?P.S.Hm, sp_releaseapplock is not compatible with the application pooling. |
 |
|
|
beegirl38
Starting Member
4 Posts |
Posted - 2007-11-21 : 13:01:53
|
| Hanging spids. Mostly displaying sp_releaseapplock_1 |
|
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-11-21 : 13:54:30
|
| Are these connections locking other ones? |
|
|
|
beegirl38
Starting Member
4 Posts |
Posted - 2007-11-21 : 15:21:03
|
| Nope |
|
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-11-21 : 15:32:21
|
| So you're just concerned with the number of 'dead' connections?It is a problem with an application. If that application 'forgets' to close the connections, you can close them on SQL server side.Select them:select * from master.dbo.sysprocesses where spid>50 -- not systemand datediff(ss,last_batch,getdate())>3600 -- idling more then 1 hourand program_name='...' -- put your bad app name, how it names itself in sysprocessesthen go in a loop and kill them using KILL command.You can write a stored proc and schedule it. |
|
|
|
|
|
|
Hanging processes