Orphaned Replica Versions in 10.2.2

[847396730]

There is a Knowledge Base article about this, but it is insufficient for versions after the SDE table restructuring.  Link

Goal: discover replica versions whose replicas have been unregistered, leaving the versions orphaned.

Method: query the versions (sde.SDE_versions) and the replicas (sde.gdb_items)--those versions with unassociated replicas are orphans.

I can find a relationship between replicas and their versions; the item ObjectID is part of the version name, as shown below.

sde.gdb_items: ObjectID = 43923

sde.SDE_versions: Name = SYNC_SEND_43923_16

But I cannot find where this same number '43923' exists on its own such that I am able to join the versions to the replicas to discover orphaned replica versions.  You can't join '43923' to 'SYNC_SEND_43923_16' and the alternative is a tedious series of LIKE/IN statements, and there must be a way the db itself correlates them.

Any ideas?

Here is the SQL I use to find replicas:

SELECT * FROM
[ESRIPROD].[sde].[GDB_ITEMS]

  where type IN('5B966567-FB87-4DDE-938B-B4B37423539D',

'D86502F9-9758-45C6-9D23-6DD1A0107B47',

'4ED4A58E-621F-4043-95ED-850FBA45FCBC')

[BlakeTerhune]

The person in my position before me used this (Oracle) SQL to view replicas and deal with orphans but I'm not exactly sure how. From what I can see, it only returns the versions that have a matching replica. So if there is a version missing, it does not have a replica and may be considered orphaned. Maybe you can refine it for your needs.

SELECT EXTRACTVALUE(XMLType(Definition), '/GPReplica/Name') AS "Replica Names", EXTRACTVALUE(XMLType(Definition), '/GPReplica/ID') AS "ReplicaID"

FROM sde.GDB_ITEMS_VW ITEMS INNER JOIN sde.GDB_ITEMTYPES ITEMTYPES ON ITEMS.Type = ITEMTYPES.UUID

WHERE ITEMTYPES.Name = 'Replica';


‍‍‍‍‍‍‍‍

[BrooksWilson]

I realize this is 2 years old, but if someone comes along looking for more information, I wanted to include this. Here is the SQL used to find orphaned replicas:

select name --delete
from sde.sde_versions where name not in (
select v.name from sde.gdb_items g join
sde.sde_versions v
on g.objectid = Left(SubString(v.name, PatIndex('%[0-9.-]%', v.name), 10)
, PatIndex('%[^0-9.-]%', SubString(v.name, PatIndex('%[0-9.-]%', v.name), 10) + 'X')-1)
where g.type = '{4ED4A58E-621F-4043-95ED-850FBA45FCBC}') and name like 'SYNC%';‍‍‍‍‍‍‍

Change the select to Delete and it will remove the orphaned replicas. 

[BlakeTerhune]

Just to clarify, that is for SQL Server, right?

[BrooksWilson]

Yes

[DrewDowling]

Thanks you for this SQL, I found it a lot more helpful than what is provided in the confusingly similar ESRI technical articles on this subject

How To: Determine if there are orphaned replica system versions in the geodatabase 

How To: Determine if there are orphaned replica system versions in the geodatabase 

One question I have is if there could be a danger of leaving other orphan data, such as orphan database states or lineages by using SQL to delete versions instead of the delete version tool. To be on the safe side I used the tool, to delete my orphans.

[BlakeTerhune]

I think any time you are rolling your own SQL to take the place of an Esri-managed operation, there is risk of it being messy. I always try to use the Esri tools if I can.

[TinaMorgan1]

Always take backups before using SQL in the repository.  We prefer this be done in test environments first, and always thoroughly test normal workflows after the SQL update.

This workflow, however, is relatively straight forward, and I have not seen any cascading effects due to deleting replica sync versions that I have confirmed (doubly if necessary) are orphaned.

If you have already unregistered the replica, then it is assumed you have already synchronized that replica, and done the appropriate maintenance to get your edits into the version you want them in (ultimately default then to base table with a compress).  If you still see your SYNC_SEND... versions in the sde_versions table after unregistering the replica, then those version could potentially block edits from being pushed through, will minimize how effective your compress will be, and over time will slow down your system.  

[TinaMorgan1]

SQL Server: 

Find orphaned sync send versions.  This will return any sync send versions that do not have a corresponding replica -  sync version # corresponds to GDB_ITEMS.ObjectID

select name from sde.sde_versions where name not in (

select v.name from sde.gdb_items g join

sde.sde_versions v

on g.objectid = Left(SubString(v.name, PatIndex('%[0-9.-]%', v.name), 10)

, PatIndex('%[^0-9.-]%', SubString(v.name, PatIndex('%[0-9.-]%', v.name), 10) + 'X')-1)

where g.type = '{4ED4A58E-621F-4043-95ED-850FBA45FCBC}') and name like 'SYNC%';

This will return all sde_version records that do not have an associated replica type in the gdb_items table.