Error / Crash when Viewing Enterprise Database Locks
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
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- 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)
