3

We have ESRI ArcMap 10.0 (build 4400) ArcGIS Desktop 10 Service Pack 5. SQL Database 10.0.4000.

We are trying to Compress our Versioned tables but for some reason it's not pulling the changed records into the SQL Tables.

I have checked the SDE_compress_log and can see the Compression was successful but the start_state_count is 5 and the end_state_count is 5 so I think this means nothing was released from the delta tables? Is that true? I also noticed that the end_state_count never seems to be zero. Why is that?

I checked the SDE_versions table and there is only 1 record with the latest state_id, however there are multiple updates that still need to be compressed. There are no related replicas. The compression worked last prior to July 7th 2016 but now nothing is updated. Should the SDE_versions table contain a record for each pending compression or just the last one?

I did some research and created the following query. It returns 5 rows. One for each pending update.

SELECT mv.[state_id],
   mv.[registration_id],
   tr.table_name,
   s.creation_time,
   sl.lineage_name,
   sl.lineage_id,
   lm.time_last_modified
FROM [sde].[SDE_mvtables_modified] mv
JOIN sde.SDE_table_registry tr ON mv.registration_id = tr.registration_id
JOIN sde.SDE_states s ON mv.state_id = s.state_id
JOIN sde.SDE_state_lineages sl ON s.lineage_name = sl.lineage_name
JOIN sde.SDE_lineages_modified lm ON sl.lineage_name = lm.lineage_name
JOIN sde.SDE_versions v ON s.state_id = v.state_id

Can anyone explain what might be happening? It's like there is an unfinished transaction that is preventing the release of the delta data.

This is what I see in my SDE_state_lineages table. Is this normal?

lineage_name lineage_id
0            0
15908        0
41477        0
15908        32512
41477        32512
15908        41276
41477        41276
41477        41548
41477        41552

This is the SDE_versions table

name    owner   version_id  status  state_id    description parent_name parent_owner    parent_version_id   creation_time
DEFAULT sde 1   1   41572   Instance default version.   NULL    NULL    NULL    2011-07-07 14:03:14.000

The table registration_id is 292 so I looked in a292 and there are 2300+ rows in there. Should this "a" table empty out once the compression has successfully pushed the data to the destination SQL table?

6
  • 1
    Pinned states are an occasional hazard in a geodatabase. The usual solution to this sort of problem is to open a Tech Support incident. Unfortunately, ArcGIS 10.0 was retired from support six months ago, so that is no longer an option. Furthermore, GIS SE is not equipped to review your data to look for issues. You would be more likely to get help here if you provide more details (exact release with service pack and patches, RDBMS product and version, the names of the tables involved, state lineage info,...) – Vince Jul 20 '16 at 16:56
  • 1
    Please always edit the question to contain details requested in comments. State lineage information resides in the sde_state_lineages table. If you have any replicas, you'll also need to provide those details as well. – Vince Jul 20 '16 at 17:21
  • Vince, I have updated my initial question with everything I can. Let me know if that helps determine what is wrong or if I need to provide more information. – Dave Stuart Jul 20 '16 at 19:27
  • Just to clarify - your sde_versions table only include 1 row where the name is DEFAULT? Therefore, you have reconciled, posted, and deleted all user versions? FYI - the end_state_count in the sde_compress_log table will only ever be reduced to '1'. – Radar Jul 21 '16 at 16:36
  • Also - I have run into cases where the compression 'hangs' or does not reduce the state count. Sometimes it helps to run compression again. – Radar Jul 21 '16 at 16:38
1

We resolved this issue by killing the locks in SDE_state_locks table. Restarting the sdeArc service on the server killed 7/10 of the locks. The remaining 3 had the SDE_state_locks.direct_connection set to Y, the other 7 were N. I don't know what that means. So then I dropped all the tempdb tables associated to these 3 remaining locks, restarted the sdeArc Service again and then they were all gone. After that were are to reach state ZERO with a compress.

Although this has resolved the issue I have no idea why these locks showed up especially when they were with a read-only account. Just as a side note we also rebooted the server on Friday and then this morning (Monday) these 10 locks showed up and were all created just after midnight. Maybe someone can add to this answer?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.