Target state not found in the STATES table

[HeatherScroggins]

I am trying to enable attachments on a feature class in our enterprise geodatabase. However, I keep getting an error that says: 'Failed to create table attachments. Target state not found in the STATES table.'

I have done a full compress on the database (removed all versions and compressed down to 1 state) and I still get the error.

Any ideas of how to troubleshoot this would be appreciated. 

Extra notes:

• database is version 10.7
• it is on SQL 2019
• I also tried to de-register the dataset as versioned and I got an error trying to do that. It wouldn't let me unregister the dataset as versioned.

[CraigRussell]

Hey Heather

You could try running the Diagnose Version Metadata and Diagnose Version Tables  geoprocessing tools to see if there are any inconsistencies.  There are corresponding repair tools in the Geodatabase Administration toolset if you do happen to find any issues, but make sure you take full backups before executing either of those tools.

Craig

[HeatherScroggins]

Hi Craig,

Thanks for pointing me to those tools. I've run the Diagnose tools for both Metadata and Tables. The tables diagnose tool said that 51 multiversioned tables had orphaned, duplicate, missing, or redundant rows. Some of the tables listed looks like all the rows in the feature class reference nonexistent states. What happens when I run the repair tool? Will it delete all those rows? I can't find good documentation about what the tool does on the backend to fix the issue.

[CraigRussell]

Hi Heather

Firstly, I'd strongly suggest that you get in touch with your local support provider, and backup your database.  Preserve incremental backups if possible, and take a snapshot that doesn't compromise the ability to do point in time restoration.

Regarding what the repair tool does, this is from old sde command line documentation, so it is likely to be reasonably consistent with what the geoprocessing tool does now.

The repair rules for the delta tables are as follows:

• Records in the Adds table that reference nonexistent states are deleted.
• Records in the Deletes table that reference nonexistent states are deleted.
• When multiple versions of a record are present in the same state, all but the latest record is deleted.
• When the same feature in the Adds or Base table is deleted multiple times in the same state, all but the latest delete entry is removed.

Hope the repair goes well and it solves your issue!

[KimGarbade]

If you run a select query on the states table in SSMS or Toad, is there only a single record referring to state 0?  

[HeatherScroggins]

Hi Kim,

Yes there is only a single record referring to state 0. Here is a screenshot of the states table from first thing this morning.

[KimGarbade]

Sorry, just a quirk of English (and it's the only language I know), but I meant is there only one record in the entire table and does it reference parent_state_id zero?  This would mean that you have reconciled and posted all child versions up to default and then compressed the database to get it to "state 0".  The reason I asked was because I got the impression that this is what you were trying to do, and if it had succeeded (and there was only state zero represented in the table) it might have given us a clue as to the "Target state not found.." message...I.E. that it didn't make a lot of sense given your database state. However, the table seems to indicate versions still exist, so the message is probably correct.  

If you think you should be at state 0 (I.E. that you should only have default and no children) then running the repair tools is probably your best option; unless you know for sure when the unreferenced state was created and how; and that restoring a backup and correcting the problem would only cost you very little work related to lost edits.... No matter what you try though please take @CraigRussell 's advise and make sure your backups are rock solid.