Error trying to view locks

16392
55
Jump to solution
12-19-2017 10:29 AM
MichaelStranovsky
Occasional Contributor

I am receiving this error while clicking on the lock tab in Geodatabase Administration using ArcCatalog 10.5.1.  Has anyone else experienced this?

55 Replies
NatalieM_Runyan
New Contributor III

I'm getting it at 10.6; my SQL Server is 2016

AdminNCCMA
New Contributor II

Also getting it with 10.6.1, SQL Server 2016

mpboyle
Regular Contributor

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'
SteveHossack
New Contributor II

Your query worked great. I was able to determine the orphaned records and fix the problem. Thank you for sharing!

MichaelLoconte
New Contributor III

Thanks for the query, it is awesome!

0 Kudos
Streltzer_Tracie
New Contributor III

I am having an issue with deadlocks at 10.4.1 on SQL Server 2016 and this is an awesome query..  Under Oracle I used to kill orphaned connections nightly and had no way to do this easily in SQL Server until I found this query.  This will become part of my regular maintenance..Thank you so much for sharing!!

0 Kudos
EvanMarshall5
New Contributor III

Thanks for sharing this query--this allowed me to find the orphaned lock and use its sde_id to delete it via a SQL server Mgmt studio.

btw we were still seeing the 'Column is null' error at arcMap 10.6.1 and Pro 2.3.2.

We can see all the locks in Pro 2.4 and don't get this error.

NickSwartz
New Contributor II

Thanks for this.  Really helped save our bacon!

0 Kudos
BillFox
MVP Frequent Contributor

The status for BUG-000110574 has been updated to Implemented.

Reproducible at ArcMap 10.6.0, 10.6.1 and Pro 2.2.0

Fix implemented in 2.3

RayChilcote
Occasional Contributor

We ran into this problem recently.  Big thanks for the references to the specific lock.  And that script!

More than handling the NULLs, what causes the NULLs?  We've dug in and found the cause... at least in our case.

Our Compress was failing.  Worse, these orphaned locks did not exist before, but did exist after.  And it also orphaned some version information, so we began losing edits.

We've had 2 items to cause this.  First, an orphaned editing service.  It was leaving locks across many feature classes.  Second, our developers that create the services would sometimes forget to uncheck the schema lock (checked by default) when creating a service.

Especially for the orphaned edit service... there are no lock references in the locks tables.  When the Compress runs, it only partially completes.  Not a partial Compress.  It broke in the middle (FAILURE -51) due to a lock it did not foresee (to skip).  Data was changed in some locks tables and failed when attempting in others, thereby creating the NULLs at hand.

So now my question: is the BUG fix going to simply not error and show NULLs?  What is BUG-000110574 going to implement?