I have had several questions on my blog post: http://blogs.msdn.com/b/psssql/archive/2010/08/03/how-it-works-error-18056-the-client-was-unabl... related to SQL Server 2008's honoring of an query cancel (attention) during the processing of the reset connection. This blog will augment my prior post.
The questions normally center around the Error 18056, State 29 and how one can encounter it. I have outlined the high level flow in the diagram below for producing the error.
The application will reuse a connection from the pool. When this occurs the client driver will set the reset bit in the TDS header when the next command is executed. In the diagram I used an ODBC example of SQLExecDirect.
The " If Cancelled " used by Redo Login is where the change occurs between SQL 2005 and SQL 2008. The cancel was not checked as frequently in SQL 2005 so it was not honored until the command execution started. SQL Server 2008 will honor the attention during the redo login processing.
Here was an example that I received that will show the behavior. Notice that the execution (rs.Open) is done asynchronously so control returns to the client as soon as the query is put on the wire to the SQL Server. The cn.Cancel following the rs.Open will submit the attention for the request that was traveling to the SQL Server. This will produce the same pattern as shown in the diagram above, interrupting the Redo Login . If you were not using pooled connections the reset activity would not be taking place and the query itself would be interrupted.
set cn = CreateObject("ADODB.Connection")
set rs = CreateObject("ADODB.Recordset")
for i = 1 to 1000
cn.Open "Provider=SQLNCLI10;Integrated Security=SSPI;Data Source=SQL2K8Server; initial catalog =whatever;"
rs.ActiveConnection = cn
rs.CursorLocation = 2
‘ 48 = adAsyncExecute + adAsyncFetch
rs.Open "select * from whatever", cn, 0, 1, 48
Internally an attention is raised as a 3617 error and handled by the SQL Server error handlers to stop execution of the request. You can see the 3617 errors in the sys.dm_os_ring_buffers. You can watch them with the trace exception events as well.
<Record id= "1715" type="RING_BUFFER_EXCEPTION" time="12558630"><Exception><Task address= 0x11B4D1B88</Task><Error>3617</Error><Severity>25</Severity><State>23</State><UserDefined>0</UserDefined></Exception><Stack
Bob Dorr - Principal SQL Server Escalation Engineer
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.