SBX - Search With Button

SBX - Forum Post Title

Management Reporter Change Tracking SQL issue

Microsoft Dynamics GP Forum

Charles Ray asked a question on 24 Jul 2014 8:52 AM
My Badges

Question Status

Verified

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.

Reply
Charles Ray responded on 24 Jul 2014 2:46 PM
My Badges
Verified Answer

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.

Reply
Harry Lee responded on 1 Sep 2015 9:58 AM
My Badges
Verified Answer

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.

Reply
Sheri Ibbotson responded on 20 Sep 2015 1:40 PM
My Badges

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

Reply
Sheri Ibbotson responded on 20 Sep 2015 1:53 PM
My Badges

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.

Reply
Charles Ray responded on 20 Sep 2015 2:15 PM
My Badges

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

Reply
Sheri Ibbotson responded on 20 Sep 2015 3:27 PM
My Badges

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.

Reply
Alvaidas Gedminas responded on 21 Dec 2015 11:06 AM

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

Thanks

Reply
Sheri Ibbotson responded on 21 Dec 2015 1:52 PM
My Badges
Verified Answer

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.

image

 

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.

image

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>

Reply
Sheri Ibbotson responded on 21 Dec 2015 1:55 PM
My Badges

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!

Reply
Alvaidas Gedminas responded on 21 Dec 2015 2:00 PM

Thanks that helped a lot.

Reply
Rob Klaproth responded on 7 Dec 2016 1:20 AM
My Badges

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.

Reply
Charles Ray responded on 24 Jul 2014 2:46 PM
My Badges
Verified Answer

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.

Reply
Harry Lee responded on 1 Sep 2015 9:58 AM
My Badges
Verified Answer

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.

Reply
Sheri Ibbotson responded on 21 Dec 2015 1:52 PM
My Badges
Verified Answer

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.

image

 

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.

image

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>

Reply

SBX - Two Col Forum

SBX - Migrated JS