I am receiving this error while clicking on the lock tab in Geodatabase Administration using ArcCatalog 10.5.1. Has anyone else experienced this?
I came across this issue in both Desktop 10.5.1 and Pro 2.2.2 using Sql Server (2014 Standard).
I put together a query to try and locate the orphaned records rather than deleting all the rows within the '_lock' tables and SDE_process_information table.
In the example below, it seems there are 2 orphaned records within the SDE_table_locks table, resulting in NULL values for the Lock Owner, which is pulled from the SDE_process_information table.
Using this query, I assume all I have to do is remove the 2 affected rows within the SDE_table_locks table, rather than truncating all '_lock' tables and the SDE_process_information table, is that correct...? All other locks are finding their corresponding parent process.
Below is the sql statement I used, which takes into account all the '_lock' tables (what is shown above is a small sample returned). Perhaps this is useful to others when trying to locate specific records rather than deleting all records within these tables.
You can view all locks on the database by uncommenting line 168 (below).
where ProcessIDFound = 'N'FYI --- this query does not translate topology and geometric network tables names, they will be returned as stored in the database (ex: T_1_DirtyArea, T_1_PointErrors, etc...)
**Note: you may need to adjust schema names AND change line 1 to the name of your enterprise geodatabase (this was written for SQL Server)**
use YourDatabaseName;
with
StateLocks as
(
select 'SDE_state_locks' as LockTable
,[l].[sde_id] AS ProcessID
,case
when [p].[sde_id] is null then 'N'
else 'Y'
end as ProcessIDFound
,[l].[state_id] as ForeignID
,case
when [s].[state_id] is null then 'N'
else 'Y'
end as ForeignIDFound
,'SDE_states' as ForeignTable
,cast([s].[state_id] as nvarchar(255)) AS ObjectName
,'Version' as ObjectType
,'state' as LockType
,[p].[owner] as LockOwner
,case [l].[lock_type]
when 'S' then 'shared'
when 'E' then 'exclusive'
end as LockMode
,[l].[lock_time] as DateAcquired
,substring([p].[nodename], 0, charindex(':',[p].[nodename])) as MachineName
,right([p].[nodename], len([p].[nodename]) - charindex(':',[p].[nodename])) as GISVersion
from
--state locks
dbo.SDE_state_locks as l
left join
--process information
dbo.SDE_process_information as p on [l].[sde_id] = [p].[sde_id]
left join
--states
dbo.SDE_states as s on [l].[state_id] = s.[state_id]
),
TableLocks as
(
select 'SDE_table_locks' as LockTable
,[l].[sde_id] as ProcessID
,case
when [p].[sde_id] is null then 'N'
else 'Y'
end as ProcessIDFound
,[l].[registration_id] as ForeignID
,case when [t].[registration_id] is null then 'N' else 'Y' end as ForeignIDFound
,'SDE_table_registry' as ForeignTable
,[t].[table_name] as ObjectName
,'Dataset' as ObjectType
,'schema' as LockType
,[p].[owner] AS LockOwner
,case [l].[lock_type]
when 'S' then 'shared'
when 'E' then 'exclusive'
end as LockMode
,[l].[lock_time] as DateAcquired
,substring([p].[nodename], 0, charindex(':',[p].[nodename])) as MachineName
,right([p].[nodename], len([p].[nodename]) - charindex(':',[p].[nodename])) as GISVersion
from
--table locks
dbo.SDE_table_locks as l
left join
--process information
dbo.SDE_process_information as p on [l].[sde_id] = [p].[sde_id]
left join
--table registry
dbo.SDE_table_registry as t on [l].[registration_id] = [t].[registration_id]
),
ObjectLocks as
(
select 'SDE_object_locks' as LockTable
,[l].[sde_id] as ProcessID
,case
when [p].[sde_id] is null then 'N'
else 'Y'
end as ProcessIDFound
,[l].[object_id] as ForeignID
,case
when [v].[version_id] is null then 'N'
else 'Y'
end as ForeignIDFound
,'SDE_versions' as ForeignTable
,[v].[owner] + '.' + [v].[name] as ObjectName
,'Version' as ObjectType
,'version' as LockType
,[p].[owner] as LockOwner
,case [l].[lock_type]
when 'S' then 'shared'
when 'E' then 'exclusive'
end as LockMode
,[l].[lock_time] AS DateAcquired
,substring([p].[nodename], 0, charindex(':',[p].[nodename])) as MachineName
,right([p].[nodename], len([p].[nodename]) - charindex(':',[p].[nodename])) as GISVersion
from
--object locks
dbo.SDE_object_locks as l
left join
--process information
dbo.SDE_process_information as p on [l].[sde_id] = [p].[sde_id]
left join
--versions
dbo.SDE_versions as v on [l].[object_id] = [v].[version_id]
),
LayerLocks as
(
select 'SDE_layer_locks' as LockTable
,[l].[sde_id] as ProcessID
,case
when [p].[sde_id] is null then 'N'
else 'Y'
end as ProcessIDFound
,[l].[layer_id] as ForeignID
,case
when [y].[layer_id] is null then 'N'
else 'Y'
end as ForeignIDFound
,'SDE_layers' as ForeignTable
,[y].[database_name] + '.' + [y].[owner] + '.' + [y].[table_name] as ObjectName
,'Layer' as ObjectType
,'layer' as LockType
,[p].[owner] as LockOwner
,case [l].lock_type
when 'S' then 'shared'
when 'E' then 'exclusive'
end as LockMode
,[l].[lock_time] as DateAcquired
,substring([p].[nodename], 0, charindex(':',[p].[nodename])) as MachineName
,right([p].[nodename], len([p].[nodename]) - charindex(':',[p].[nodename])) as GISVersion
from
--layer locks
dbo.SDE_layer_locks as l
left join
--process information
dbo.SDE_process_information as p on [l].[sde_id] = [p].[sde_id]
left join
--states
dbo.SDE_layers as y on [l].[layer_id] = [y].[layer_id]
)
--select orphaned locks
select *
from
(
--state locks
select * from StateLocks
union all
--table locks
select * from TableLocks
union all
--object locks
select * from ObjectLocks
union all
--layer locks
select * from LayerLocks
) as q1
where ProcessIDFound = 'N'
