topic Re: Error / Crash when Viewing Enterprise Database Locks in ArcGIS Pro Questions

Error / Crash when Viewing Enterprise Database Locks

mpboyle — Fri, 14 Sep 2018 14:09:03 GMT

Does anybody else have an issue where when trying to view enterprise locks, Pro displays the errors below and crashes?

This doesn't happen on all our enterprise databases, just certain ones. Is there something specific in the database to look for where this is happening?

System Information:

Pro version: 2.2.2

RDBMS: Sql Server 2014 Standard

Re: Error / Crash when Viewing Enterprise Database Locks

MichaelVolz — Fri, 14 Sep 2018 14:14:22 GMT

Check out this thread as it appears to occur in both ArcMap and Pro:

https://community.esri.com/thread/206938-error-trying-to-view-locks

Re: Error / Crash when Viewing Enterprise Database Locks

JonathanFarmer_oldaccount — Fri, 14 Sep 2018 14:53:12 GMT

Hi Matt,

We have a bug logged for this issue and it is going to be addressed. I don't have a version that it will be fixed at yet though. We have seen this error in Desktop as well so it's not specific to Pro.

Take a look in the SDE_table_locks, SDE_layer_locks, SDE_object_locks, SDE_state_locks and SDE_process_information tables and see what is there. There may be an orphaned entry hanging things up. But do not modify anything in those tables without a full backup of your database.

BUG-000110574: The error, “Column value is null” is encountered whi..

Jonathan

Re: Error / Crash when Viewing Enterprise Database Locks

mpboyle — Sun, 12 Dec 2021 05:35:54 GMT

I put together some queries in Sql Server to try and locate the orphaned locks, so I could hopefully narrow down which specific records are the issue, and what table they are located within.

Below is a screenshot of the end result query showing there seem to be 2 orphaned lock records within the SDE_table_locks table. The sde_id value found in the SDE_table_locks table is NOT found within the SDE_process_information table ... therefore resulting in NULL values for the Lock Owner.

Is it safe to assume, these are the only 2 records that need to be removed, rather than deleting all records within all the lock tables and SDE_process_information table?

Below is the entire Sql statement, 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 add the where clause below to the end of the query to filter ONLY the orphan records (if any). The LockTable field should point you to the table where the orphans are found, with the ProcessID value equaling the sde_id value of the orphans.

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**

SELECT *
FROM (

/* STATE LOCKS */

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
--process information
LEFT JOIN dbo.SDE_process_information AS p ON l.[sde_id]=p.[sde_id]
--states
LEFT JOIN dbo.SDE_states AS s ON l.[state_id]=s.[state_id]


UNION ALL


/* TABLE LOCKS */

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.[database_name] + '.' + t.[owner] + '.' + t.[table_name] AS ObjectName,
          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
--process information
LEFT JOIN dbo.SDE_process_information AS p ON l.[sde_id]=p.[sde_id]
--table registry
LEFT JOIN dbo.SDE_table_registry AS t ON l.[registration_id]=t.[registration_id]


UNION ALL


/* OBJECT LOCKS */

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
--process information
LEFT JOIN dbo.SDE_process_information AS p ON l.[sde_id]=p.[sde_id]
--versions
LEFT JOIN dbo.SDE_versions AS v ON l.[object_id]=v.[version_id]


UNION ALL


/* LAYER LOCKS */

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 
--process information
LEFT JOIN dbo.SDE_process_information AS p ON l.[sde_id]=p.[sde_id] 
--states
LEFT JOIN dbo.SDE_layers AS y ON l.[layer_id]=y.[layer_id]

) AS lk‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Error / Crash when Viewing Enterprise Database Locks

JonathanFarmer_oldaccount — Tue, 18 Sep 2018 19:59:26 GMT

Hi Matt,

Just to confirm, the ProcessID for those 2 table locks does not exist in the process_information table? Does the ENGDUSTCONTROL feature class still exist in the database? This lock does not show up in the Geodatabase Administration dialog in ArcMap?

We certainly don't want to go deleting everything from the locks table as some of those are valid locks. Actually, we never want to delete anything from the database but it does sound like you may have a couple of orphans here.

My recommendation would be to take a full backup of this database and restore it into another SQL instance. This way no other users are connected or will connect and there are no services or replicas hitting this geodatabase. You can then make sure that no locks or connections exist in the Geodatabase Administration dialog in ArcMap and also that the SDE_Process_Information table is empty. Once that is done, if the 2 locks for ENGDUSTCONTROL are still present, you can remove them from the SDE_Locks table in the database. Then get back into ArcMap and test to see if the errors are still present.

But you may very well be running into that bug I mentioned and this isn't an issue of orphaned locks. If that is the case, this workflow above won't help. Also feel free to open a case with Support as we can work with you more closely and take a database backup in house to go through these workflows before we ask you to do them yourself to make sure they work on our end. Something to consider.

Jonathan

Re: Error / Crash when Viewing Enterprise Database Locks

mpboyle — Tue, 25 Sep 2018 14:29:11 GMT

Jonathan Farmer‌

Those 2 highlighted ProcessID values do NOT exist within the SDE_process_information table (first screenshot)
The engDustControl feature class DOES still exist
These 2 locks DO show in the Administer Geodatabase window if using an older version of ArcMap (10.3) (highlighted blue in second screenshot)

Re: Error / Crash when Viewing Enterprise Database Locks

JonathanFarmer_oldaccount — Tue, 25 Sep 2018 15:05:43 GMT

Matthew,

Thanks for this information. Given that we see some different behavior in ArcMap 10.3 vs. ArcGIS Pro and also because we appear to have some records in the database which may or may not be orphaned, I think we need to get a case created with Tech Support to troubleshoot further. Is this something you'd be open to doing?

Jonathan

Re: Error / Crash when Viewing Enterprise Database Locks

Javier_AntonioEscudero — Mon, 15 Apr 2019 14:05:03 GMT

Thanks Matthew,

Your sql script worked very well for me. After delete orphaned lock records from tables locks, error 'Colum value is' dissapears from Administration Locks Menu.

Regards,

Javier Escudero