Client has one company database that will not back up. The error is "Failed to flush the commit table to disk in dbid ## due to error 2601. (SQL Error:3999)" This is a SQL bug related to change tracking that was fixed in SQL 2008R2 SP1RU4. They were on SP1, and I have since updated the instance to SP2. The error has not gone away, and the database still will not back up. The workaround is to remove change tracking, back up, re-enable change tracking. The problem is that I need to turn off change tracking individually on tables within the database before I can disable change tracking. the sys.change_tracking_enabled view lists 21 table ids but not table names.
Has anyone had this issue, and is there a list of tables with change tracking enabled for MR? Possibly a script that will change all referenced tables to disabled? I can re-enable the company in MR Configuration Console once the backup problem is resolved.
Any help is appreciated.
The issue is resolved. I was able to match the sys.change_tracking_tables object_id with GP table names.
They are (for future reference):
I went into each table and set change tracking from True to False for each of the tables, then turned off change tracking for the database.
The database backup was successful, so I went into the MR Configuration Console and used 'Reconfigure Change Tracking'. I verified that the routine had turned on change tracking for the database (it had), then was able to back up successfully with change tracking on.
Thanks for posting the list of tables where change tracking is enabled for a typical company database. I have since encountered a situation where the client's DYNAMICS database experienced the same problem, and had to identify the tables where change tracking were enabled in the DYNAMICS database, in order to perform the same steps you did (disable change tracking for all tracked tables, before disabling change tracking for the database).
Here is a query, which can be used to find the names of the tracked tables in SQL,this should be run in the problem database.
SELECT * FROM sys.change_tracking_tables sctt
left join sys.tables st on sctt.object_id = st.object_id
Presently, the tables enabled for change tracking in the DYNAMICS database are:
Hope this helps anyone encountering the same problem.
I just finished a live upgrade and now MR is giving me this message: Change tracking is not enabled on DYNAMICS for the following tables: SY01500. To enable incremental reading of data, reconfigure change tracking in the Configuration Console.
I cannot turn it on in MR it just hangs.
Its turned in Sql for the database and I turned it on for that table
worse yet -I cannot release the system to go live without this resolved
this didn't occur in test - but it did in Live
I read Harry Lee's post more closely and see that I just needed to enable it for the 3 tables listed in his post.
Everything worked after enabling Change Tracking on the other 2 tables?
Yes Sir - it did. I enabled the change tracking option to TRUE for each of those tables and that resolved it. I did restart the services in the console as well and no error were reported, the MR CU13 reports on GP2015R2 are fully operational now. I also ran the script to increase to update the scheduling interval and set it 5.
Sorry for the stupid question. But command did you guys run to enable change tracking on MC00200 table ?
Related Article: http://community.dynamics.com/gp/f/32/t/136038
Although issue should have been resolved already in SQL 2008R2 SP1RU4 I still received this with later versions.
Method 1: Open up Sql server management studio and change tracking on each of the tables below.
Right click on the table>>Properties>>Change Tracking, Click on Change Tracking on the right side of the window, change from False to True, Click OK.
Repeat this for all tables.
Method 2: Run the scripts below
Run the script below against the Company database
ALTER TABLE dbo.GL00100
ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);
ALTER TABLE dbo.GL00102
ALTER TABLE dbo.GL00200
ALTER TABLE dbo.GL00201
ALTER TABLE dbo.GL10000
ALTER TABLE dbo.GL10001
ALTER TABLE dbo.GL10100
ALTER TABLE dbo.GL10101
ALTER TABLE dbo.GL12000
ALTER TABLE dbo.GL12001
ALTER TABLE dbo.GL20000
ALTER TABLE dbo.GL30000
ALTER TABLE dbo.GL32000
ALTER TABLE dbo.GL40000
ALTER TABLE dbo.GL40200
ALTER TABLE dbo.MC00200
ALTER TABLE dbo.MC40000
ALTER TABLE dbo.MC40600
ALTER TABLE dbo.SY00300
ALTER TABLE dbo.SY40100
ALTER TABLE dbo.SY40101
Run the script below against the Dynamics database
ALTER TABLE dbo.MC00100
ALTER TABLE dbo.SY01500
If after running the above scripts and issue still persists, then there might be other tables that you need to change tracking on. The best way to find this is to open up the Data Mart Integration logs.
Open Management Reporter Configuration Console, on the left panel under Logs, click on Data Mart Integration. Go through the logs on the right side of the window to find out what other table you need to change the tracking on.
Hope this Helps!
Inserted from <https://community.dynamics.com/gp/b/dynamicscloud/archive/2015/05/22/mr-datamart-integration-failing-or-not-starting-after-a-new-installation-or-upgrade-error-tracking-is-not-enabled>
if you click the images link in the reply above is will open the image in the
hope this helps you! Happy Holidays!
Thanks that helped a lot.
Really the best way to handle this if you have GP2015 or higher is to go into each company and turn "enable gl reporting" off, and click OK out of the window and then go back in and "enable GL reporting" checked back on and click ok.
This will do all the SQL work on the back end for you and you won't have to worry about missing certain tables.
I think a lot of people have issues because they try to turn on the enableGLreporting=1 from SQL for their companies.
The "Reconfigure change tracking" rarely works.
Business Applications communities