How Enabling Change Tracking Can Cause Backup Failure

By Lori Brown | Backup and Restore

Mar 08

Recently I had a client who contacted me because all of the sudden the backup for a couple of databases started to fail. While those backups had been running successfully in the past they suddenly started failing with this message:

Blog_20180308_1

With a little more digging he found more info. Here is more verbiage from the error message:

Blog_20180308_2

Now we have gotten somewhere. This error is commonly associated when change tracking has been enabled on databases. I found that for Microsoft Dynamics and Management Reporter application databases that change tracking must be enabled and is commonly set for about 21 tables. Sure enough, the databases that had the backup failures were Dynamics databases. Apparently the data in the sys.syscommittab system table can get corrupt in some way. The recommended fix is to disable change tracking, take a backup of the databases then enable change tracking once again. Backups should be successful after that.

https://support.microsoft.com/en-us/help/3083381/duplicate-key-rows-from-the-sys-syscommittab-table-in-sql-server

Since change tracking is not just for Dynamics databases, and could potentially be found on tons of tables, I thought that I would make a bit of code that would find all the tables that have change tracking enabled and create all of the disable\enable statements necessary to fix this issue and get backups working again.

I tested this out on my local system. Here is how I did it.

Enable change tracking on my database.

ALTER DATABASE [ADMIN] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

Create a few tables. Some have change tracking enabled and some do not. I also set different settings for TRACK_COLUMNS_UPDATED so that I could be sure that all settings were correctly captured and the enable statements would put everything back as it had been initially found.

USE [ADMIN]

GO

CREATE TABLE [dbo].[CTTest](

[Column1] [int] NOT NULL,

[Column2] [varchar](50) NULL,

[Column3] [date] NULL,

CONSTRAINT [PK_CTTest] PRIMARY KEY CLUSTERED

(

[Column1] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

 

CREATE TABLE [dbo].[CTTest2](

[Column1] [int] NOT NULL,

[Column2] [varchar](50) NULL,

[Column3] [date] NULL,

CONSTRAINT [PK_CTTest2] PRIMARY KEY CLUSTERED

(

[Column1] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

 

CREATE TABLE [dbo].[CTTest3](

[Column1] [int] NOT NULL,

[Column2] [varchar](50) NULL,

[Column3] [date] NULL,

CONSTRAINT [PK_CTTest3] PRIMARY KEY CLUSTERED

(

[Column1] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

 

I know….I am not very creative when making test tables. Each table must have a primary key in order to enable change tracking. After that, I went to the CTTest, CTTest3 & CTTest4 table properties and set change tracking up. I turned on TRACK_COLUMNS_UPDATED for CTTest & CTTest4 but turned it off for CTTest3.

Blog_20180308_3

Here is my code designed to find the tables with change tracking enabled and create the disable\enable statements needed to turn it all of then turn it all back on again. No statements are executed against the database. Print statements are used instead to create the statements you need so that you can control what happens and when. Run this while connected to the user database that is the target.

DECLARE @dbname VARCHAR(128)

DECLARE @AutoClnup TINYINT

DECLARE @AutoClnupStr VARCHAR(3)

DECLARE @RetPer INT

DECLARE @RetPerUnitsDesc VARCHAR(60)

DECLARE @schema VARCHAR(128)

DECLARE @tblname VARCHAR(128)

DECLARE @ColUpdtFlg TINYINT

DECLARE @ColUpdt VARCHAR(5)

DECLARE @sqlstr1 NVARCHAR(2000)

DECLARE @sqlstr2 NVARCHAR(2000)

DECLARE @sqlstr3 NVARCHAR(2000)

DECLARE @sqlstr4 NVARCHAR(2000)

 

DECLARE tblcur CURSOR FOR

SELECT S.name AS SchemaName,

OBJECT_NAME(T.object_id) AS TableName,

T.is_track_columns_updated_on

FROM sys.change_tracking_tables T

INNER JOIN sys.tables TT ON TT.object_id = T.object_id

INNER JOIN sys.schemas S ON S.schema_id = TT.schema_id

ORDER BY SchemaName, TableName

 

SET @dbname = DB_NAME()

SELECT @AutoClnup = is_auto_cleanup_on,

@RetPer = retention_period ,

@RetPerUnitsDesc = retention_period_units_desc

FROM sys.change_tracking_databases WHERE database_id = DB_ID()

 

IF @AutoClnup = 1

SET @AutoClnupStr = ‘ON’

ELSE

SET @AutoClnupStr = ‘OFF’

 

OPEN tblcur

 

FETCH NEXT FROM tblcur INTO @schema, @tblname, @ColUpdtFlg

 

— disable statements

PRINT ‘–BEGIN DISABLE CHANGE TRACKING STATEMENTS’

WHILE @@FETCH_STATUS=0

BEGIN

 

SET @sqlstr1 = ‘ALTER TABLE [‘+@schema+‘].[‘+@tblname+‘] DISABLE CHANGE_TRACKING’

 

PRINT @sqlstr1

 

FETCH NEXT FROM tblcur INTO @schema, @tblname, @ColUpdtFlg

END

CLOSE tblcur

 

SET @sqlstr2 =

ALTER DATABASE [‘+@dbname+‘] SET CHANGE_TRACKING = OFF’

PRINT @sqlstr2

PRINT ‘–END DISABLE CHANGE TRACKING STATEMENTS

 

— open it again to create enable statements

OPEN tblcur

 

FETCH NEXT FROM tblcur INTO @schema, @tblname, @ColUpdtFlg

 

— enable statements

PRINT ‘–BEGIN ENABLE CHANGE TRACKING STATEMENTS’

SET @sqlstr3 = ‘ALTER DATABASE [‘+@dbname+‘] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = ‘+CAST(@RetPer AS VARCHAR(4))+‘ ‘+@RetPerUnitsDesc+‘, AUTO_CLEANUP = ‘+@AutoClnupStr+‘)

 

PRINT @sqlstr3

 

WHILE @@FETCH_STATUS=0

BEGIN

IF @ColUpdtFlg = 1

SET @ColUpdt = ‘ON’

ELSE

SET @ColUpdt = ‘OFF’

 

SET @sqlstr4 = ‘ALTER TABLE [‘+@schema+‘].[‘+@tblname+‘] ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ‘+@ColUpdt+‘)’

 

PRINT @sqlstr4

 

FETCH NEXT FROM tblcur INTO @schema, @tblname, @ColUpdtFlg

END

PRINT ‘–END DISABLE CHANGE TRACKING STATEMENTS’

 

CLOSE tblcur

DEALLOCATE tblcur

GO

 

 

Here is the output:

Blog_20180308_4

You can copy and paste the statements you need into a query window and run them to disable\enable change tracking. Or you can save the output to a file so that you have a script saved for just in case you need it.

I decided to also put a bit of data into one of the tables so that I could check to see what happens to the hidden tables when you go about disabling and enabling change tracking.

insert into CTTest values (1, ‘Value1’, ‘1-1-2018’)

insert into CTTest values (2, ‘Value2’, ‘1-2-2018’)

insert into CTTest values (3, ‘Value3’, ‘1-3-2018’)

insert into CTTest values (4, ‘Value4’, ‘1-4-2018’)

insert into CTTest values (150, ‘Value5’, ‘1-5-2018’)

update CTTest set Column2 = ‘Val1’ where Column3 = ‘1-1-2018’

delete from CTTest where Column3 = ‘1-5-2018’

Using a query from super smart Kendra Little (@Kendra_Little or https://littlekendra.com/ ), I can see that my internal tables are being tracked.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

GO

select sct1.name as CT_schema,

sot1.name as CT_table,

ps1.row_count as CT_rows,

ps1.reserved_page_count*8./1024. as CT_reserved_MB,

sct2.name as tracked_schema,

sot2.name as tracked_name,

ps2.row_count as tracked_rows,

ps2.reserved_page_count*8./1024. as tracked_base_table_MB,

change_tracking_min_valid_version(sot2.object_id) as min_valid_version

FROM sys.internal_tables it

JOIN sys.objects sot1 on it.object_id=sot1.object_id

JOIN sys.schemas AS sct1 on

sot1.schema_id=sct1.schema_id

JOIN sys.dm_db_partition_stats ps1 on

it.object_id = ps1. object_id

and ps1.index_id in (0,1)

LEFT JOIN sys.objects sot2 on it.parent_object_id=sot2.object_id

LEFT JOIN sys.schemas AS sct2 on

sot2.schema_id=sct2.schema_id

LEFT JOIN sys.dm_db_partition_stats ps2 on

sot2.object_id = ps2. object_id

and ps2.index_id in (0,1)

WHERE it.internal_type IN (209, 210);

GO

Blog_20180308_5

When I ran the statements to disable change tracking the same query shows that no tables are being tracked:

Blog_20180308_6

My client reported that disabling\enabling change tracking did indeed fix his backup problem. The fun part is that while several databases had change tracking enabled, only a few had the backup issue.

Here are some of the links that I thought were interesting while researching:

https://www.brentozar.com/archive/2014/06/performance-tuning-sql-server-change-tracking/

https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-tracking-sql-server

https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-tracking-sql-server

https://msdn.microsoft.com/en-us/library/dn932130.aspx

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!

>