Why does it take deleting a version to move some records from the A and D table to the business table

1707
7
11-30-2017 02:17 PM
RandyKreuziger
Occasional Contributor III

We have a versioned SDE 10.2 geodatabase in SQL Server 2012 with users running ArcMap 10.4.1.  There are a dozen versions directly  off the default with no child versions.  Users reconcile, post, reconcile before the database is compressed once a week.  One of the users was looking at the data via the Microsoft SQL Server Management Studio and noticed that records were missing from some of the business tables.   After looking into it I discovered that in some cases there are over 3000 records in a couple of the A tables going back to December of 2016.  

I restored a copy of the most recent version of the database to development and went through reconcile / post / compress on all the versions.  a handful of records moved to the business tables but most of those in the A tables remained in the A tables.  Only after deleting all the versions were the remaining records moved from the A tables to the business tables.  After deleting all the versions the record counts are all correct and the A / D tables are now empty.  Anyone ideas on why the versions would have to be deleted for those records to finally move?  Thanks

Tags (3)
0 Kudos
7 Replies
Madanbhurati1
New Contributor III

Hi,

Make sure all the versions are reconciled and posted to parent version, check the SDE_State_ID field of Add table (A), weather all the states are same and  try to disconnect all the connected users using arcpy.DisconnectUser(Admin connection,"All") script from database Admin Login. finally compress the Geodatabase. 

I hope all the records from delta tables will be moved to base table( business table).

0 Kudos
RandyKreuziger
Occasional Contributor III

Madan,

I restored the database to DEV so there were no users to disconnect.  The only thing I didn't do was check the SDE_State_ID field.  It was only after deleting the versions that the remaining records were moved from the Add tables and Delete tables to the base tables.

0 Kudos
Madanbhurati1
New Contributor III

Generally, I would check the Reconcile Order sub-tab in Versions tab of Geo-database administration dialog box by connecting as Admin. it  shows all versions blocking the default version from achieving a compress which would move the Default version to state 0. if any version is listed there I would reconcile/Post it again and compress the Geo-database. 

it moves all the records from delta tables to base tables.

0 Kudos
George_Thompson
Esri Frequent Contributor

After you run the reconcile/post of all the versions, you need to run a compress on the geodatabase. The compress process will flush out any "stale" records in the A/D tables.

Geodatabase compression—Help | ArcGIS Desktop 

https://community.esri.com/groups/geodatabase?sr=search&searchId=d70da03d-f3aa-4532-9552-b87f0fbae62...https://community.esri.com/community/gis/enterprise-gis?sr=search&searchId=d4ea2984-574a-4df9-8697-6...

--- George T.
0 Kudos
RandyKreuziger
Occasional Contributor III

George,

  I must not have been clear in my original post but, yes, I did do the compress after the reconcile posts.  That did not move the stale records out of the A and D tables. 

0 Kudos
George_Thompson
Esri Frequent Contributor

Sorry, I missed that in the second paragraph.

Can you please provide the last entry in the SDE_COMPRESS table?

Do you have any replica's?

--- George T.
AhmadSALEH1
Occasional Contributor III

Randy,

After you compress your database and before going to SQL server to view the business table, Try to disconnect the database from the ArcCatalog (By right click on the DB and select Disconnect), then try to open the business table and see what happens.

0 Kudos