SQL Server session kill, has been in the Killed/rollback state situation analysis [go]

This article will describe the SQL Server session for you to kill, has been in the Killed/rollback State Situation analysis, tutorial how to:

Today, a very strange situation, found a session exception, this session is simply executing a simple stored procedure, which uses the linked server (Linked server) to query another server data (stored procedures do not have any explicit transactions, UPDATE, delete operations, There are only a few simple select queries, two of which use the linked server linked server, because of the production environment, not good to post SQL statements, in the DPA monitoring tool, found that the session caused a very long OLE DB wait time, manually perform the test, Discovery does not take a long time, after the session has been kill, the rollback state has completed until 0%, and the estimated remaining time has been 0 seconds. As shown below:

?

Code

1	KILL 129 WITH STATUSONLY;

?

Code

1

?

Code

1	SPID 129: 正在进行事务回滚。估计回滚已完成: 0%。估计剩余时间: 0 秒。

As shown below, the start_time of this session (Timestamp when the request arrived. is not nullable.) For 2016-10-18 02:17:58.210, until now 2016-10-19 16:02:30.173 has been dozens of hours, my kill session time Point is 2016-10-19 12:00:01.

You can see that it waits for the OLE DB type (Figure one), which means waiting for the server to return the result. In addition, the transaction_type of this transaction is 2, which means that this is just a read-only transaction (avoid misunderstanding, this is an evidence), Transaction_state is 2, indicating that the transaction is active (the Transaction is active). This point in time of the transaction caught my attention because the server that the linked server was pointing to was down (refer to the link VMware Platform Windows Server 2012 unresponsive outage), just when the server virtual machine was down. Restart at a point in time (the server 1 o'clock in the morning more downtime, 2:22am when the restart). From the DPA monitoring tool you can also see that the point is really there. As shown below:

This kind of distributed query, because linked server refers to an exception (such as downtime), the session process on this side has been waiting for its return results, but the linked server refers to the servers as an exception can never give this session process feedback any results, this happens, However, it is a bit odd that this situation cannot be ended by a kill session. The problem can only be resolved by restarting the server, nor by the kill process (because SQL Server is a single-process multithreaded architecture, unlike Oracle's multi-process architecture, which can kill processes or threads from the operating system level (Windows platform, Oracle provides a tool Orakill utility can kill threads), but restarting the database is a cumbersome task. So this zombie session has been in the database, for my obsessive-compulsive person, watching its existence, always want to kill it. It's really a torture thing. Is this SQL Server tutorial useful to you?

SQL Server session kill, has been in the Killed/rollback state situation analysis [go]