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
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).
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.
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.
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
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.
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?
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.