Deleting schema locks

19017
25
10-03-2016 01:10 PM
forestknutsen1
MVP Regular Contributor

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

Tags (2)
0 Kudos
25 Replies
George_Thompson
Esri Frequent Contributor

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.

--- George T.
JoshWhite
Regular Contributor III

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

Josh White, AICP
Principal Planner

City of Arkansas City
0 Kudos
George_Thompson
Esri Frequent Contributor

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 

--- George T.
0 Kudos
JoshWhite
Regular Contributor III

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

Josh White, AICP
Principal Planner

City of Arkansas City
0 Kudos
George_Thompson
Esri Frequent Contributor

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.

--- George T.
BlakeTerhune
MVP Regular Contributor

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.

0 Kudos
JoshWhite
Regular Contributor III

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?

Josh White, AICP
Principal Planner

City of Arkansas City
0 Kudos
BlakeTerhune
MVP Regular Contributor

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.

0 Kudos
RhettZufelt
MVP Frequent Contributor

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_

0 Kudos
JoshWhite
Regular Contributor III

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.  

Josh White, AICP
Principal Planner

City of Arkansas City
0 Kudos