Answered by:
KPID SPID state ?

Question
Answers
-
Hello,
I guess you are talking about the result of the system view sys.sysprocesses (Transact-SQL)? First, this view is already deprecated, so don't use it any longer.
KPID presents the Windows Thread id and you are wondering why a SQL Server session which don't belong to a Windows thread have an open transaction? Everything starts a transaction (=>ACID) in SQL Server, so even system processes can have open transactions.
Olaf Helper
[ Blog] [ Xing] [ MVP]- Marked as answer by Fanny LiuMicrosoft contingent staff, Moderator Sunday, December 15, 2013 12:17 PM
-
- Marked as answer by SQLguy1001 Sunday, December 22, 2013 7:58 PM
-
Yes, I was looking into the conditions why KPID =0 and at the sametime I see Open_tran>0 and server not doing anything SPid in Sleeping state, so I concur that if SPIDs are in Running state, KPID is allocated (Worker thread is allocated); Does this hold correct.
If you see sleeping SPIDs with open_tran>0, awaiting command and sleeping, this is a clear indication the session has an open transaction. Like Erland, I suspect this might not be intentional and the root cause might be that the application is not managing transactions and query timeouts properly. This can result in the "hung application" symptom as well as the symptom of many connections with open transactions.
I once experienced a scenario very much like this. The application used a home-grown connection pool rather than leveraging connection pool feature of the client API (which does a better job of cleaning up connection state). Some stored procedures had explicit transactions.
After a timeout occurred executing a proc with an explicit transaction, the application did not know to execute a rollback because the transaction was started in the procedure code. All subsequent activity on that connection after the timeout was inadvertently done in the original transaction context, resulting in much blocking and inadvertently nested transactions. Only a SQL Server restart could correct the problem.
We then added SET XACT_ABORT ON to all stored procedures with explicit transactions. This solved the open transaction problem because the transaction was automatically rolled back upon receiving the attention event from the client after the timeout.
Dan Guzman, SQL Server MVP, http://www.dbdelta.com
- Marked as answer by SQLguy1001 Tuesday, December 24, 2013 6:00 PM
-
If the application experiences a query timeout, it should always respond with "IF @@trancount > 0 ROLLBACK TRANSACTION".
It is also a good idea to issue "SET XACT_ABORT ON" upon connection as well as include it in all stored procedure as Dan says. But the application should not assume that just because it issued SET XACT_ABORT ON when it connected it does not have to roll back, since some SQL code for whatever reason my fiddle with the setting.
This is a typical example of defensive program. The less various components in a system trust each other, the more stable the overall system will be.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se- Marked as answer by SQLguy1001 Thursday, December 26, 2013 10:34 AM
All replies
-
Hello,
I guess you are talking about the result of the system view sys.sysprocesses (Transact-SQL)? First, this view is already deprecated, so don't use it any longer.
KPID presents the Windows Thread id and you are wondering why a SQL Server session which don't belong to a Windows thread have an open transaction? Everything starts a transaction (=>ACID) in SQL Server, so even system processes can have open transactions.
Olaf Helper
[ Blog] [ Xing] [ MVP]- Marked as answer by Fanny LiuMicrosoft contingent staff, Moderator Sunday, December 15, 2013 12:17 PM
-
- Marked as answer by SQLguy1001 Sunday, December 22, 2013 7:58 PM
-
Yes, I was looking into the conditions why KPID =0 and at the sametime I see Open_tran>0 and server not doing anything SPid in Sleeping state, so I concur that if SPIDs are in Running state, KPID is allocated (Worker thread is allocated); Does this hold correct.
Sysprocesses still very useful (may be deprecated, but very reliable). Ms should continue with it, if it can continue with undocumented stuff from Sybase Era. -
-
I have seen such situation and was wondering why SQL Server is not able to handle that.
400 Spids doing nothing - all in sleeping state , 1 in rollback sate with waittype logbuffer??Are the sleeping spids all awaiting command? In that case, it simply means the application is connected but not currently executing a query on the connection. There is nothing SQL Server does except wait for the application to execute another command. It's up to the client application to handle the connection management. It would be bad if SQL Server unilaterally closed the client connection on the server side.
It is common for a rolling back spid show logbuffer waits.
Dan Guzman, SQL Server MVP, http://www.dbdelta.com
-
Yes, I was looking into the conditions why KPID =0 and at the sametime I see Open_tran>0 and server not doing anything SPid in Sleeping state, so I concur that if SPIDs are in Running state, KPID is allocated (Worker thread is allocated); Does this hold correct.
Sysprocesses still very useful (may be deprecated, but very reliable). Ms should continue with it, if it can continue with undocumented stuff from Sybase Era.Awaiting for Reply !!!
-
I have seen such situation and was wondering why SQL Server is not able to handle that.
400 Spids doing nothing - all in sleeping state , 1 in rollback sate with waittype logbuffer??Are the sleeping spids all awaiting command? In that case, it simply means the application is connected but not currently executing a query on the connection. There is nothing SQL Server does except wait for the application to execute another command. It's up to the client application to handle the connection management. It would be bad if SQL Server unilaterally closed the client connection on the server side.
It is common for a rolling back spid show logbuffer waits.
Dan Guzman, SQL Server MVP, http://www.dbdelta.com
-
We was not there to tell what happened, but my diagnosis is that they did not really stop the application (of which the architecture is completely unknown to me). Then again, if you manage to stop the application in such away that the network library does not notice that the other end is gone, how would SQL Server to know.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se -
We was not there to tell what happened, but my diagnosis is that they did not really stop the application (of which the architecture is completely unknown to me). Then again, if you manage to stop the application in such away that the network library does not notice that the other end is gone, how would SQL Server to know.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
- Edited by SQLguy1001 Monday, December 23, 2013 9:47 AM spelling error
-
Yes, if the application is incorrectly coded and does not handle query timeouts correctly (it needs to issue "IF @@trancount > 0 ROLLBACK TRANSACTION"), this can cause a lot of problems, particulary if the application uses the connected model. You get one session with a orphan transaction holding locks. Other processes gets blocked by that process, times out, etc. And then if these process jog along and perform updates, there is quite some work to rollback when the disconnect.
SQL Server is able to handle the disconnections, and according to the testimony it did it the correct way. The more interesting question is why there were so many processes with open transactions when the application waved bye-bye.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se -
Yes, if the application is incorrectly coded and does not handle query timeouts correctly (it needs to issue "IF @@trancount > 0 ROLLBACK TRANSACTION"), this can cause a lot of problems, particulary if the application uses the connected model. You get one session with a orphan transaction holding locks. Other processes gets blocked by that process, times out, etc. And then if these process jog along and perform updates, there is quite some work to rollback when the disconnect.
SQL Server is able to handle the disconnections, and according to the testimony it did it the correct way. The more interesting question is why there were so many processes with open transactions when the application waved bye-bye.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
- Edited by SQLguy1001 Monday, December 23, 2013 5:53 PM wanted to explain further
-
It could certainly have helped if you had given the full scenario from the beginning. Whether SQL Server actually got stuck is hard to tell. It appears that there was some massive rollback activity going on that server.
Restarting helped, because in this case the rollback is handled by recovery which is usually faster.
But the application needs to be fixed to prevent this from happning again.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se -
Yes, I was looking into the conditions why KPID =0 and at the sametime I see Open_tran>0 and server not doing anything SPid in Sleeping state, so I concur that if SPIDs are in Running state, KPID is allocated (Worker thread is allocated); Does this hold correct.
If you see sleeping SPIDs with open_tran>0, awaiting command and sleeping, this is a clear indication the session has an open transaction. Like Erland, I suspect this might not be intentional and the root cause might be that the application is not managing transactions and query timeouts properly. This can result in the "hung application" symptom as well as the symptom of many connections with open transactions.
I once experienced a scenario very much like this. The application used a home-grown connection pool rather than leveraging connection pool feature of the client API (which does a better job of cleaning up connection state). Some stored procedures had explicit transactions.
After a timeout occurred executing a proc with an explicit transaction, the application did not know to execute a rollback because the transaction was started in the procedure code. All subsequent activity on that connection after the timeout was inadvertently done in the original transaction context, resulting in much blocking and inadvertently nested transactions. Only a SQL Server restart could correct the problem.
We then added SET XACT_ABORT ON to all stored procedures with explicit transactions. This solved the open transaction problem because the transaction was automatically rolled back upon receiving the attention event from the client after the timeout.
Dan Guzman, SQL Server MVP, http://www.dbdelta.com
- Marked as answer by SQLguy1001 Tuesday, December 24, 2013 6:00 PM
-
Yes, I was looking into the conditions why KPID =0 and at the sametime I see Open_tran>0 and server not doing anything SPid in Sleeping state, so I concur that if SPIDs are in Running state, KPID is allocated (Worker thread is allocated); Does this hold correct.
If you see sleeping SPIDs with open_tran>0, awaiting command and sleeping, this is a clear indication the session has an open transaction. Like Erland, I suspect this might not be intentional and the root cause might be that the application is not managing transactions and query timeouts properly. This can result in the "hung application" symptom as well as the symptom of many connections with open transactions.
I once experienced a scenario very much like this. The application used a home-grown connection pool rather than leveraging connection pool feature of the client API (which does a better job of cleaning up connection state). Some stored procedures had explicit transactions.
After a timeout occurred executing a proc with an explicit transaction, the application did not know to execute a rollback because the transaction was started in the procedure code. All subsequent activity on that connection after the timeout was inadvertently done in the original transaction context, resulting in much blocking and inadvertently nested transactions. Only a SQL Server restart could correct the problem.
We then added SET XACT_ABORT ON to all stored procedures with explicit transactions. This solved the open transaction problem because the transaction was automatically rolled back upon receiving the attention event from the client after the timeout.
Dan Guzman, SQL Server MVP, http://www.dbdelta.com
-
If the application experiences a query timeout, it should always respond with "IF @@trancount > 0 ROLLBACK TRANSACTION".
It is also a good idea to issue "SET XACT_ABORT ON" upon connection as well as include it in all stored procedure as Dan says. But the application should not assume that just because it issued SET XACT_ABORT ON when it connected it does not have to roll back, since some SQL code for whatever reason my fiddle with the setting.
This is a typical example of defensive program. The less various components in a system trust each other, the more stable the overall system will be.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se- Marked as answer by SQLguy1001 Thursday, December 26, 2013 10:34 AM