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:
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
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)
My Scenario: Server OS: Windows Server 2016 Datacenter
SQL Server 2016 Standard, SP2 Applied
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
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”:
Scroll down to “Microsoft SQL Server 2016 (64-Bit)
Select “Service Pack 2 for SQL Server 2016 (KB4052908) (64-bit)
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