Answered

Management Reporter Change Tracking SQL issue

(0) Share

Report

Posted on by Charles Ray

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.

*This post is locked for comments

I have the same question (0)

All responses (11)

Answers (3)

Verified answer

Charles Ray on at

Like (1)

Report

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):

GL00100

GL00102

GL00200

GL00201

GL10000

GL10001

GL10100

GL10101

GL12000

GL12001

GL20000

GL30000

GL32000

GL40000

GL40200

MC00200

MC40000

MC40600

SY00300

SY40100

SY40101

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.

Was this reply helpful? Yes No
Verified answer

Redbeard 12,931 on at

Like (0)

Report

Charles -

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:

MC00100

MC40200

SY01500

Hope this helps anyone encountering the same problem.

Was this reply helpful? Yes No
Sheri Ibbotson 712 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Sheri Ibbotson 712 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Charles Ray on at

Like (0)

Report

Everything worked after enabling Change Tracking on the other 2 tables?

Was this reply helpful? Yes No
Sheri Ibbotson 712 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Alvaidas Gedminas 122 on at

Like (0)

Report

Sorry for the stupid question. But command did you guys run to enable change tracking on MC00200 table ?

Thanks

Was this reply helpful? Yes No
Verified answer

Sheri Ibbotson 712 on at

Like (2)

Report

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.

Solution

Method 1: Open up Sql server management studio and change tracking on each of the tables below.

GL00100,GL00102,GL00200,GL00201,GL10000,GL10001,GL10100,GL10101,GL12000,GL12001,GL20000,GL30000,GL32000,GL40000,GL40200,MC00200,MC40000,MC40600,SY00300,SY40100,SY40101.

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);

GO

ALTER TABLE dbo.GL00102

ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);

GO

ALTER TABLE dbo.GL00200

ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);

GO

ALTER TABLE dbo.GL00201

ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);

GO

ALTER TABLE dbo.GL10000

ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);

GO

ALTER TABLE dbo.GL10001

ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);

GO

ALTER TABLE dbo.GL10100

ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);

GO

ALTER TABLE dbo.GL10101

ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);

GO

ALTER TABLE dbo.GL12000

ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);

GO

ALTER TABLE dbo.GL12001

ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);

GO

ALTER TABLE dbo.GL20000

ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);

GO

ALTER TABLE dbo.GL30000

ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);

GO

ALTER TABLE dbo.GL32000

ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);

GO

ALTER TABLE dbo.GL40000

ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);

GO

ALTER TABLE dbo.GL40200

ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);

GO

ALTER TABLE dbo.MC00200

ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);

GO

ALTER TABLE dbo.MC40000

ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);

GO

ALTER TABLE dbo.MC40600

ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);

GO

ALTER TABLE dbo.SY00300

ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);

GO

ALTER TABLE dbo.SY40100

ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);

GO

ALTER TABLE dbo.SY40101

ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);

GO

Run the script below against the Dynamics database

ALTER TABLE dbo.MC00100

ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);

GO

ALTER TABLE dbo.SY01500

ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);

GO

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>

Was this reply helpful? Yes No
Sheri Ibbotson 712 on at

Like (1)

Report

if you click the images link in the reply above is will open the image in the

example: www.dynamicscloud.com/.../image2.png

hope this helps you! Happy Holidays!

Was this reply helpful? Yes No
Alvaidas Gedminas 122 on at

Like (0)

Report

Thanks that helped a lot.

Was this reply helpful? Yes No