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...
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-ccb5a... and discuss this with an analyst.
I'm not that great at the back end of SQL. How do I check on this to see how to change this?
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
Ouch, that would take hours with as many services that I have.
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.
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.
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?
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.
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.
R_
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.