Menu
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:
With a little more digging he found more info. Here is more verbiage from the error message:
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.
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.
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:
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
When I ran the statements to disable change tracking the same query shows that no tables are being tracked:
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://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!