After applying SQL Server 2016 SP2 Backups of Databases that had SQL Change Tracking enabled at the time SP2 was applied to the server no longer work.  Backups will fail with this message:

image

TITLE: Microsoft SQL Server Management Studio
——————————

Backup failed for Server ‘ServerName’.  (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=13.0.15700.28+((SSMS_Rel).160810-2135)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476

——————————
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

Failed to flush the commit table to disk in dbid 10 due to error 3601. Check the errorlog for more information. (Microsoft SQL Server, Error: 3999)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.5026&EvtSrc=MSSQLServer&EvtID=3999&LinkId=20476

——————————
BUTTONS: OK
——————————

My Scenario: Server OS: Windows Server 2016 Datacenter

SQL Server 2016 Standard, SP2 Applied

Databases affected are those that had SQL Change Tracking enabled at the time of SQL Server 2016 SP2 being applied to the server, like this:
image

Ola Hallengren Backup Scripts Also Fail

If you happen to be using the Ola Hallengren DatabaseBackup SPROC, you may also notice that SPROC start to fail with error

image

Msg 207, Level 16, State 1, Line 1 Invalid Column name ‘modified_extent_page_count’.

That failure is happening because there is no column ‘modified_extent_page_count’ in the DatabaseName.sys.dm_db_file_space_usage views after the application of SQL 2016 SP2.

This is the specific line from the DatabaseBackup SPROC that fails:

SET @CurrentCommand07 = ‘SELECT @ParamAllocatedExtentPageCount = SUM(allocated_extent_page_count), @ParamModifiedExtentPageCount = SUM(modified_extent_page_count) FROM ‘ + QUOTENAME(@CurrentDatabaseName) + ‘.sys.dm_db_file_space_usage’

Solution: Remove SP2:Click Start, type “Update” and then choose “View Installed Updates”:

image

Scroll down to “Microsoft SQL Server 2016 (64-Bit)

image

Select “Service Pack 2 for SQL Server 2016 (KB4052908) (64-bit) 

Click Uninstall

image

If as in my case a restart is required, the setup will tell you that.  Restart the server then repeat this process and you’ll be able to click through the wizard to the end.

When the service pack removal is complete, backups will work once again.

Disabling change tracking does not solve this problem only uninstall of SP2 worked for me

I’m adding the steps to disable change tracking here for your easy reference, but in my experience this DOES NOT actually solve the problem.  Using a script like this you can disable change tracking:

SELECT ‘ALTER TABLE dbo.’ + QuoteName(t.name) + ‘ DISABLE CHANGE_TRACKING’ as tSQLToRun
FROM sys.change_tracking_tables ctt
JOIN sys.tables t
    ON t.object_id = ctt.object_id
JOIN sys.schemas s
    ON s.schema_id = t.schema_id
ORDER BY s.name, t.name

–Copy the results of the script above to a new query window and execute to turn SQL Change Tracking off on the tables for which it was enabled, and then run a command like this to disable change tracking on the database.

ALTER DATABASE DatabaseName SET CHANGE_TRACKING = OFF

Advertisements