FIX: It takes a long time to roll back a batch that updates a large amount of data in SQL Server 2016

Applies to: SQL Server 2016SQL Server 2016 DeveloperSQL Server 2016 Enterprise

Symptoms


Assume that you install Microsoft SQL Server 2016 on a server that its memory size is very big. When you roll back a batch that updates a large amount of data, it will take a long time to finish.

Note If you restart the server, the rollback will finish in 1 hour.

Cause


This issue occurs because rollback operation holds lots of X locks on the database table. Thus the business operation on the table cannot be continued.

Resolution


Service pack information for SQL Server 2016

This update is fixed in the following service pack for SQL Server:

       Service Pack 2 for SQL Server 2016

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.