topic Re: Deleting schema locks in Geodatabase Questions

Deleting schema locks

forestknutsen1 — Mon, 03 Oct 2016 20:10:55 GMT

I am wondering how people are deleting schema locks? We are on oracle 11g and SDE 10.1. I have been using a sql delete query run from TOAD to do it for users that have read only connections...

Re: Deleting schema locks

ChristianWells — Mon, 03 Oct 2016 20:42:07 GMT

Schema locks should only be managed through the ArcGIS clients. If locks need to be removed, the supported and recommended way is to disconnect users via the Geodatabase Administration window in ArcGIS Desktop.

Manage geodatabase locks—Help | ArcGIS for Desktop

What is happening that requires locks to be removed via SQL? Are they being orphaned?

Re: Deleting schema locks

forestknutsen1 — Tue, 04 Oct 2016 21:59:09 GMT

Christian Wells wrote:
Schema locks should only be managed through the ArcGIS clients.

That, in a nutshell, is what our technical lead said. But it does not work on our system.

Even if the ArcCatalog approach was working as expected it would still be inefficient. Sometimes I will have 200 locks that I need to kill in order to update a database. Selecting them by hand off the lock table would be tiresome and prone to error. What are the other options? SDE command line? arcpy?

Christian Wells wrote:
Are they being orphaned?

No, they are not being orphaned.

Re: Deleting schema locks

ChristianWells — Sun, 12 Dec 2021 10:32:27 GMT

Thanks Forest, you should be able to remove all the users pretty quickly using the following arcpy command.

gdb = r'Database Connections\w1041.sde'
arcpy.DisconnectUser(gdb, 'ALL')‍‍‍‍

DisconnectUser—ArcPy Functions | ArcGIS for Desktop

The SDE command line tools have a similar function, but as they are deprecated, it may be best to start working with the arcpy module.

Re: Deleting schema locks

BlakeTerhune — Tue, 04 Oct 2016 23:02:31 GMT

We're on Oracle 11g and 10.2.2 and have always been able to disconnect everyone either using ArcCatalog administration or arcpy.DisconnectUser(). Not sure if you noticed but when you're in the Connections tab of geodatabase administration, you can Ctrl+A all connections and right click to disconnect all (although you have to Ctrl+click to deselect your own connection). You can also do this when viewing the Locks tab and disconnect everyone with locks on a particular feature class.

Re: Deleting schema locks

JoshWhite — Fri, 31 Mar 2017 21:57:19 GMT

But how do you keep the users from reconnecting. I have the box unchecked that says the Geodatabase is accepting new connections and yet my GIS server reconnects on longer GPs.

Re: Deleting schema locks

George_Thompson — Mon, 03 Apr 2017 11:48:09 GMT

ksjosh82‌ are the connections that are reconnecting using the SDE users as their connection properties in the GP tools?

Re: Deleting schema locks

JoshWhite — Mon, 03 Apr 2017 14:31:13 GMT

It looks like they are DBO:

Re: Deleting schema locks

George_Thompson — Mon, 03 Apr 2017 14:35:42 GMT

My guess is that if they are DBO then they have permissions to connect regardless if you have the option selected:

The geodatabase administrator can always make a connection to the geodatabase regardless of how this property is set.

Prevent geodatabase connections—Help | ArcGIS Desktop

Manage connections in SQL Server—Help | ArcGIS Desktop

Re: Deleting schema locks

JoshWhite — Mon, 03 Apr 2017 14:39:11 GMT

The thing is, the machine is just as it says my GIS server. So the only thing that should be causing locks at all is the GIS services. Seems a shame to have to shut down all services to make a simple schema change to one feature class.

Re: Deleting schema locks

George_Thompson — Mon, 03 Apr 2017 14:43:33 GMT

You may want to update the connection properties to a non-DBO user, then you can prevent the connections when you need to do schema changes.

Schema locking—ArcGIS Help | ArcGIS Desktop

There may be unintended issues with the services after schema changes. You may want to open a support case with https://community.esri.com/groups/technical-support?sr=search&searchId=42170fa1-7e43-4b27-a8dc-ccb5af4ed19b&searchIndex=0‌ and discuss this with an analyst.

Re: Deleting schema locks

JoshWhite — Mon, 03 Apr 2017 16:57:15 GMT

I'm not that great at the back end of SQL. How do I check on this to see how to change this?

Re: Deleting schema locks

George_Thompson — Mon, 03 Apr 2017 17:02:25 GMT

This would be checked via the MXD that the map service was created off of. You would need to update the connections properties of the layer and republish the map. This may include registering the new data sources with your ArcGIS Enterprise (Portal and/or Server) setup.

Connect to Microsoft SQL Server from ArcGIS—Help | ArcGIS Desktop

Preconfigure database connection files—Help | ArcGIS Desktop

Re: Deleting schema locks

JoshWhite — Mon, 03 Apr 2017 19:49:56 GMT

Ouch, that would take hours with as many services that I have.

Re: Deleting schema locks

George_Thompson — Mon, 03 Apr 2017 19:52:28 GMT

I can understand that. I would always recommend not using the DBO/SDE (geodatabase admin) account for anything but geodatabase administration to avoid these type of locks in the future.

Re: Deleting schema locks

RhettZufelt — Thu, 20 Jul 2017 22:47:56 GMT

I see this is an older thread, but not sure if you resolved this issue.

I am on 10.2, so can't test with 10.1, but there is an option in the service to Not Lock the schema with service:

This way, I can keep my services running and do any schema changes I want.

However, if you do make changes to the schema, you will need to re-publish the service for it to see the changes.

Re: Deleting schema locks

JoshWhite — Fri, 21 Jul 2017 13:58:34 GMT

The issue is that my services ignore all attempts to keep them from reconnecting before I can complete the operation whether it be a schema lock or an overwrite. The only effective way I have found to eliminate this locking problem is to stop all services, run my process and then restart the services. I did go through and make sure locking was disabled (its easier in Server Manager at 10.5.1 BTW) and unchecked the box to allow new connections and the locks came right back.

Re: Deleting schema locks

BlakeTerhune — Fri, 21 Jul 2017 15:11:57 GMT

It's pretty easy to change all of the data sources at once with the Set Data Source(s) tool in ArcCatalog. Once the MXD data sources have been changed to a user that is not DBO, just share it as a service and overwrite the existing service. Now, your Prevent Geodatabase Connections will actually stop the services from reconnecting.

Re: Deleting schema locks

JoshWhite — Fri, 21 Jul 2017 15:28:56 GMT

I'm confused, how do I go about doing that? How do I change the user to non-DBO? With OS authentication instead of DB?

Re: Deleting schema locks

BlakeTerhune — Fri, 21 Jul 2017 16:28:02 GMT

If you have database authentication, you can create a new database user for your services. Your database administrator should be able to help you with that. If it's OS authentication, get a "service" user account created and use that.