KB3168793 - FIX: Cannot create or delete a table or index when another DDL transaction is running on the same database in SQL Server 2016

Symptoms

When you perform the DDL operations such as create index or truncate table in SQL Server 2016, the operations may be blocked if there is another DDL transaction running on the same database.

Resolution

The issue was first fixed in the following cumulative update of SQL Server:

• Cumulative Update 1 for SQL Server 2016

More Information

When a DDL creates or deletes a table or an index, it needs to insert or delete the table's or index's rowset id from sysrowsets, for which it needs to hold a row level X lock on the entry. When this is done inside a transaction the X lock will be held for the duration of the transaction.

When Tuple Mover discovers flushable delete buffers it needs to iterate over sysrowsets to discover existing tables or indexes. To read each row from sysrowsets he needs to hold a row level S lock on the entry it currently reads. The discovery process stops when Tuple Mover finds the first rowset it can do work on. At this point Tuple Mover will do the actual work of flushing the delete buffer, but in order to make sure that the discovered rowset is still valid it will hold a SCH-S lock on all rowsets it evaluated during the discovery process until it finishes his work.

Sysrowsets is ordered by the rowset id, thus it can happen that Tuple Mover scanned multiple existing rowsets on which it has no action to do, but will still hold SCH-S lock and it arrives to an entry in sysrowsets for which it cannot acquire row level S lock, because it is currently being altered by a long running transaction that holds an X lock on the row. At this point DDLs involving rowset id change are blocked behind Tuple Mover's SCH-S locks.

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.