Susantha Bathige
Susantha Bathige was born in Matara, Sri Lanka and is currently working in the United States for Pearson North America as a Senior DBA III, production DBA. He has over a decade of experience in the IT industry in full scale software companies including local and international. He has completed all the levels of British Computer Society (BCS) examinations and earned a PGD (Professional Graduate Diploma) which is the academic equivalent to a university honors degree in England. He earned his Master of Science degree from Sri Lanka Institute of Information Technology (SLIIT). Since 2000 his entire career has been data based. He has worked with SQL Server from version 6.5 and specialize in many areas of SQL Server including its internals, optimization, troubleshooting, replication, and more.
4 comments
This is a great walkthrough of Change Tracking but I’m not seeing troubleshooting steps as implied by the article title.
I found a potential issue with Change Tracking that has been plaguing our organization for years. If Logging is not set to simple, the log file can explode in size very quickly, forcing production databases to have to disable change tracking cleanup. This KB may finally fix that: https://support.microsoft.com/en-us/topic/kb4500403-fix-tlog-grows-quickly-when-you-run-auto-cleanup-procedure-in-sql-server-2014-2016-and-2017-41a5a303-b0b3-e9d8-a540-597ad27b584b
I am also getting the error:
Internal Change Tracking table name : ifts_comp_fragment_1245820096_35780
Msg 207, Level 16, State 1, Line 2
Invalid column name ‘sys_change_xdes_id’.
Msg 207, Level 16, State 1, Line 13
Invalid column name ‘sys_change_xdes_id’.
CT internal tables are not accessible via normal connections, meaning a SELECT will not work against the internal tables. You need to run a SELECT for internal tables using the DAC (Dedicated Admin Connection) connection.