SQL Server does not finish execution of a large batch of SQL statements

Symptoms

When you execute a large batch of SQL statements that returns many result sets, Microsoft SQL Server may stop processing the batch before all statements in the batch are executed. The effects of this behavior depend on what operations the batch statements perform. For example, if the batch starts a transaction at the beginning and commits the transaction at the end, the commit may not occur. This behavior causes locks to be held longer than expected. This can also cause the transaction to be rolled back when the connection is closed. If the batch does not start a transaction, the symptoms of the problem may be that some statements are not executed.
When processing the results of a batch, SQL Server fills the output buffer of the connection with the result sets that are created by the batch. These result sets must be processed by the client application. If you are executing a large batch with multiple result sets, SQL Server fills that output buffer until it hits an internal limit and cannot continue to process more result sets. At that point, control returns to the client. When the client starts to consume the result sets, SQL Server starts to execute the batch again because there is now available memory in the output buffer.

Workaround

To work around the problem, use one of the following methods:
  • Method 1: Flush all the output result sets. As soon as all output result sets are consumed by the client, SQL Server completes executing the batch.
    • If you are using Microsoft Open Database Connectivity (ODBC) to connect to SQL Server, you can call the SQLMoreResults method until the method reports that there are no more result sets.
    • If you are using Microsoft OLE DB to connect to SQL Server, you can repeatedly call the IMultipleResults::GetResult method until it returns DB_S_NORESULT .
  • Method 2: Add the statement SET NOCOUNT ON to the beginning of your batch. If the batch is executed inside a stored procedure, add the statement to the beginning of the stored procedure definition. This prevents SQL Server from returning many types of result sets. Therefore, it can reduce the data to be output to the output buffer of the server. However, this does not guarantee that the problem will not occur. It only increases the chance that the data that is returned from the server is small enough to fit into one batch of result sets.
Notes
  • Microsoft recommends that you always consume all result sets from SQL Server regardless of the size of the batch that you are executing. If you do not flush this data and there are successful result sets to be returned ahead of the error in the result set batch, the client might not discover the server errors.
  • Client applications should flush the result sets to guarantee correct execution.

Status

This behavior is by design.

More Information

Steps to reproduce the problem


The problem is most easily reproduced when you are connected to the SQL Server by using the Named pipes protocol or the Shared memory (LPC) protocol. This is because of the internal buffer size that SQL Server has available for the different protocols.

The following are the possible effects of this problem. The effects are varied and depend on exactly what your batch contains.

  • Consider that a batch of database query statements is executed from an application. If the batch of database query statements is made up of a "BEGIN TRANSACTION" at the beginning and "COMMIT TRANSACTION" at the end, the commit operation may not occur even though the control is returned to the application. This is an issue because the locks that are possibly being held may cause a pending transaction and may remain unnoticed.

    In this scenario, because the transaction is never committed in the batch, it remains pending and is rolled back on disconnection from the SQL Server.
  • If you use an application program interface (API) to begin and commit your transaction, you may see the following behavior:
    • If you use the API to send a notification to the server to start a transaction, and then you execute the batch, SQL may process only a part of the batch, and then return the control to the application.
    • After this step, if you use the API to commit the transaction, only the part of the batch that was processed is committed. No error occurs.

      For example, with ODBC you call SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF) to start the transaction, and then you use SQLEndTran(SQL_COMMIT) to commit the transaction.

References

For additional information about commands that generate multiple results, visit the following Microsoft Developer (MSDN) Web sites:For additional information about how to process multiple result sets in OLE DB, visit the following MSDN Web site:Note If you are using ADO, calling the NextRecordset method of the Recordset object causes the OLE DB provider to execute the IMultipleResults::GetResult method.

For additional information about how to process multiple results in ODBC, visit the following MSDN Web site:For additional information about the query execution statement SET NOCOUNT, visit the following MSDN Web site:For more information about the ODBC SQLMoreResults API, visit the following MSDN Web site:For more information about the GetResult method of the OLE DB IMultipleResults interface, visit the following MSDN Web site:
Propriedades

ID do Artigo: 827575 - Última Revisão: 26 de ago de 2008 - Revisão: 1

Comentários