FIX: Schema lock occurs on a table with large data when you delete lots of rows from it in SQL Server 2012


Microsoft distributes Microsoft SQL Server 2012 fixes as one downloadable file. Because the fixes are cumulative, each new release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2012 fix release.
Symptoms
You have a table that contains many rows and a full-text index in Microsoft SQL Server 2012. When you try to delete lots of rows from the table in a single transaction, a schema lock occurs when rows are removed from the full-text index. Additionally, all the other manipulation queries are blocked by the delete operation.
Cause
This issue occurs because of an issue that occurs when you delete lots of rows from a very large table that has a full-text index. In this situation, the delete process on the full-text index is performed in a nested transaction. The delete operation for the full-text index can take a long time to finish even though the delete operation finishes quickly for the rows in the base table. This reverts any deletion that was made on the full-text index if the server is shut down or restarted. It also causes many blocks on the base table and many transaction locks.

Cumulative Update information

This issue was first fixed in the following cumulative update of SQL Server.
Recommendation: Install the latest cumulative update for SQL Server

Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
References
Learn about the terminology that Microsoft uses to describe software updates.
Properties

Article ID: 3068468 - Last Review: 07/23/2015 20:19:00 - Revision: 2.0

Microsoft SQL Server 2012 Analysis Services, Microsoft SQL Server 2012 Business Intelligence, Microsoft SQL Server 2012 Developer, Microsoft SQL Server 2012 Enterprise, Microsoft SQL Server 2012 Express, Microsoft SQL Server 2012 Standard

  • kbqfe kbfix kbexpertiseinter kbsurveynew KB3068468
Feedback