FIX: Error 3314 May Occur with Simultaneous DDL and DML Statements on SQL Server 2000 Service Pack 1 (310468)

SYMPTOMS

Error 3314 may occur and the database may go offline if all of the following conditions are true:

SQL Server 2000 Service Pack 1 (SP1) is the current server version.
Logged operations on SQL Server 2000 are currently in a rollback state.
Data Definition Language (DDL) changes for a particular table occur simultaneously with Data Manipulation Language (DML) statements such as an INSERT, DELETE, and UPDATE for the same table.
The transaction log for the database is near capacity before the rollback begins.

The error message that occurs is:

Msg 3314, Level 21, State 4: Error while undoing logged operation in database 'databasename'. Error at log record ID (12:3096:2).

If you receive the 3314 error but your circumstances do not match the symptoms in this article, search the Microsoft Knowledge Base for other potential causes of this error message.

CAUSE

This particular error message occurs when the log runs out of space during a rollback. Typically, the cache is flushed after the number of transactions cached reaches a certain threshold. The flush generally occurs in a separate transaction and it requires log space. During a rollback, explicit flushing does not occur; therefore, you will not see the error message with every rollback. However, under certain conditions the flush does occur. For example, if a database is busy with several transactions that involve data inserts and DDL statements are performed on the same table at the same time, the flush occurs. The flush of the cache does requires log space. If the transaction rolled back is large enough, the log may run out of space during the rollback, and the rollback fails with the error message shown in the "Symptoms" section.

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

WORKAROUND

To avoid the 3314 error, keep DML statements separated so that they do not occur when the creation of indexes and other DDL statements occur.

You are more likely to experience this problem if you use your database extensively and several types of activities occur on the same table at the same time. For example, if INSERTS, DELETES and index creations on the same table all typically take place at the same time, the rollback of any one of the data insertions or deletions may cause the 3314 error.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.