Schema Locks in Multi-User SDE

Question

We recently upgraded to AGS10, and simultaneously moved from Shapefiles to a Multi-User SDE.  We have had issues over the last 4 weeks attempting to update data in SDE.  I have been editing data in a second instance of SDE then attempting to replace our published data with the production SDE but continually run into Schema locks, whether there are any users logged on or not. Sometimes, the schema locks are on my PC, even if I am the only user, and I can't update the Features.  
Additionally, I recently got an error message that "The index passed is not within a valid range"

I have tried making the changes first thing in the morning (before other users are here), end of the day (after users go home), and on the weekend when no users are working, all with the same results.

Any input would be appreciated.

StephanieSnider · Answer

I have problems with schema locks too.  The feature classes that sometimes have locks are not versioned or a part of any replication process.  A computer (sometimes a user PC, sometimes a server) will have a lock on a feature class without having ArcGIS open.  Even with all users logged off, all map services shut down, I can still see the connection coming from the computer.  I try to use the SDE kill command, but it always says "failed to execute".  Sometimes I can kill the connection as a task in the task manager (gsrvr process) from the ArcSDE server.  But most the time I have to stop and start SDE to remove the lock.  Sometimes still, even this doesn't work, and I have to restart the offending computer that ArcSDE says has a connection.  This last work around does the trick which tells me that the computer itself is maintaining some connection lock to ArcSDE even if ArcGIS is not open on that machine.

I understand the importance of schema locks with a multi-user versioned database, but it appears I am not the only one that is frustrated by dealing with random locks that can't be disconnected.  I have the GDBT Toolkit for ArcCatalog installed but it is really not useful as a database administration tool.  Please please ESRI, give us more tools for managing ArcSDE....especially for schema locks!

AnastasiaAourik · Answer

I have the following python script...
This leaves behind a schema lock on the resulting sde featureclass so the next day it runs, 
it fails.
How, in python, can I clear a schema lock

Note: My older code used to do a 
delete layer
then recreate the layer 
and no arcpy.env.overwriteoutput=true
and this does not leave behind a schema lock.
Something with the overwriteoutput is leaving 
the lock around...
# ---------------------------------------------------------------------------
# gisbuild_layer.py
# Created on: 2011-09-29 11:35:46.00000
#   (generated by ArcGIS/ModelBuilder)
# Author: Anastasia Aourik
# Description: 
# Regenerate featureclass WG_POMDJOBS from source table with xy values
# ---------------------------------------------------------------------------

# Import arcpy module
import arcpy, os, datetime
arcpy.env.overwriteOutput = True

# Local variables:
WCSS_SOURCE = "Database Connections\OLE DB ConnectionMYDRS0.odc\WCSS.POMD_JOBREC_MV"
WCSS_Layer = "WCSS_Layer"
SIIGIS_WG_LAYER = "Database Connections\Connection to sql04.sde\siiview.SIIGIS.WELLSGENERAL\siiview.SIIGIS.WG_POMDJOBS"
now = datetime.datetime.now()

print "GisBuild of PATHFINDER JOBS"
print "Started: " + str(now)

try:

if arcpy.Exists(WCSS_SOURCE):
    # Process: Make XY Event Layer
    print "Source Table Exists"
    arcpy.MakeXYEventLayer_management(WCSS_SOURCE, "LONGITUDE_NBR", "LATITUDE_NBR", WCSS_Layer, "GEOGCS['GCS_WGS_1984',DATUM['D_WGS_1984',SPHEROID['WGS_1984',6378137.0,298.257223563]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]];-400 -400 1000000000;-100000 10000;-100000 10000;8.98315284119522E-09;0.001;0.001;IsHighPrecision", "")
    print "Make XY Layer Succeeded"
    if arcpy.Exists(WCSS_Layer):
        print "Proceed to Copy Features"
        # Process: Copy Features
        print "Begin Copy Features Management"
        arcpy.CopyFeatures_management(WCSS_Layer, SIIGIS_WG_LAYER, "", "0", "0", "0")
        print "All Features Copied
"
    now = datetime.datetime.now()
    print "Ended: " + str(now) + "
"

except: 
   arcpy.AddError("Python Messages: " + arcpy.GetMessages())

Jonathan DeRose · Answer

Hi Karim,

I am sorry to hear of the difficulties you are encountering while editing within your ArcSDE geodatabase.  Can you please provide some additional information on your configuration and the processes you are running when seeing the schema lock error?
* What types of updates are being executed?
* Do any map services pull data from either ArcSDE instance?

The following links may be useful if you have not already referenced them:

FAQ:  What schema changes can be made to versioned data?
http://support.esri.com/en/knowledgebase/techarticles/detail/29885

FAQ:  Why does ArcGIS Server lock the ArcSDE schema?
http://support.esri.com/en/knowledgebase/techarticles/detail/32536

FAQ:  How are the various lock mechanisms implemented in ArcSDE and the geodatabase?
http://support.esri.com/en/knowledgebase/techarticles/detail/27034