Allow Users To Break Locks in a SQL Server Geodatabase Without the processadmin Server Role

506
2
07-14-2021 03:12 PM
Status: Open
Labels (1)
jmweidler
New Contributor II

I work for a State government, and we commonly store GIS data in SQL server geodatabases. Our typical workflow is to assign databases by agency. This means multiple agencies can have databases on the same SQL server, but they can't access each other's data. A problem we constantly run into is with breaking locks. We have popular datasets that might be viewed by hundreds of people at a time. Even just viewing a dataset creates a lock that doesn't allow any schema changes to be made (e.g., adding and deleting fields, enabling archiving, versioning, etc.). Currently, to break locks in a SQL database from ArcGIS Pro you have to have the processadmin server role. The problem with this is that it is a SQL server-wide role. Our IT database administrators DO NOT want to give this role out to agency users. So power users at our agencies are constantly frustrated that they can't make some of these needed changes to their data very easily. I propose removing the requirement to drop connections in order to break locks. @ColinZwicker 

2 Comments
BillFox

That would break any read consistency protections built into the database and could lead to data corruption issues.

How about something like an agreed upon weekly maintenance window on Thursday's from 16:00 to 18:00?

jmweidler

I guess I'm not sure what you mean by read consistency protections. The lock records are in a handful of tables (e.g., SDE_layer_locks, SDE_table_locks, etc.), so it should be just a matter of deleting the appropriate records in those tables for whatever layer you're working on. I've seen people do this sort of thing manually without data corruption, so it would be nice if Pro did it this simpler way too.