Compress Geodatabase Tool

22612
45
04-20-2017 11:00 AM
Labels (1)

Compress Geodatabase Tool

When trying to compress a geodatabase it's difficult to remove all of the locks within the geodatabase, especially when you have ArcGIS Server services accessing the data.  This tool will shutdown all of your ArcGIS Server services, disconnect all connected geodatabase users, block further connections, reconcile/post all versions (with the option to delete the versions), compress the database, and then start all of the services.  There is also an option to send an e-mail of the compress results.

 

Currently, this has been tested with SQL Server, PostgreSQL, and Oracle.  Also, note that if you have geodatabase replicas, these should be synchronized before this tool is executed.  After the tool has executed, you should rebuild indexes and update statistics.

 

The tool can be easily run as a script as well by commenting out the GP variables in the 'Compress Database State 0.py' file and populating the commented variables at the beginning of the script.  This will allow you to easily automate this using the Windows Task Scheduler.

 

Update 6/13/18:  Added the ability to select which services to stop/start during the compress operation.  Also, the tool now outputs a CSV file reporting the before/after counts of the delta (A & D) tables for the versioned feature classes.  Ex:

Labels (1)
Attachments
Comments

is there anyone made the attach script work on your environment??

Hi Addisalemtamiru‌, what is the issue you are running into?  Are you receiving any errors?

Hi jake skinner, I am recieving the below error 

Runtime error
Traceback (most recent call last):
File "<string>", line 112, in <module>
File "<string>", line 56, in getStateID
File "c:\program files (x86)\arcgis\desktop10.3\arcpy\arcpy\arcobjects\mixins.py", line 915, in __init__
_BaseArcObject.__init__(self, *gp_fixargs((server, instance, database, user, password), True))
File "c:\program files (x86)\arcgis\desktop10.3\arcpy\arcpy\arcobjects\_base.py", line 47, in __init__
for arg in args))
RuntimeError: Object: Connection error Entry for SDE instance not found in services file

any suggestion??

Appears the script is having trouble querying your enterprise geodatabase.  What database are you using (Oracle, SQL Server)?  Can you send a screen shot of how you have the tool setup?

I am using Oracle ArcSDE. see the below scrrenshot.

Looks like the username you are using for your Oracle instance is unable to query the sde.states table.  Is your geodatabase connection using the SDE user?

Here's some of my experience working with compressing an enterprise geodatabase with a regularly schedule Python script.

https://community.esri.com/message/597806 

Jake,

I'm having issues with the Portal Server and ArcGIS Server as user input strings. What format should these be, \\itxxapp998 (machine name) or as a url?

Michael Miller‌ take a look at the Tool Help (button on the lower right hand corner of the tool).  When you click on the parameter it will give you examples of these.  They will be only the server name (i.e. server1.domain.com).

Thanks Jake, I was aware and referenced that doc. I'm attempting to use the following format and it is not working. (itdhq1apx999.itd.state.id.us)Compress dialog box

I do not understand why it's not running.

Can you post a screen shot of the error you are receiving?

Error dialog....

Looks like you are getting a certificate error.  You are specifying port 6443, so the request is made through HTTPS to stop/start the services.  ArcGIS Server has a self-signed certificate that will not be trusted.  I'll have to update the code to ignore the untrusted certificates.  In the meantime, you can do the following:

1.  Specify port 6080 (if HTTP is enabled for ArcGIS Server)

2.  Import a CA certificate into ArcGIS Server's Administrative Directory, and configure ArcGIS Server to use this certificate.  See steps 1 & 2 in the below link:

Configuring HTTPS using an existing certificate—ArcGIS Server Administration (Windows) | ArcGIS Ente... 

That did the trick.  Thanks for the assistance @Jake Skinner!

Hello Jake,

Thank you for providing such a wonderful tool!  I do have a couple questions...

#1 in the python script, line 19 for the "services = arcpy.GETParametersAsText (9) - where would I get this list and what is the format?

#2 in the python script, line 32 for the "serviceList = services.split (";") - will i list the services here?  where do i get this list and what is the format?

#3 in the python script, line 35 for the "versionedFeatureClasses = { }  - also where is this list found and what format should i write it in?

Also - on line 14, it requires the agsServer name... we have two servers who are redundant to each other.  Is there a way to include both servers so that none of the services are left on?

Run the tool in ArcGIS Desktop and then you can copy the result as a python snippet in the Geoprocessing > Results window by right-clicking on the result.

You will get all the parameters.  Ex:

arcpy.CompressUpdate_Geodatabase_Maintenance(Database_Admin_Connection="Database Connections/SQLSERVER - DBO@VECTOR.sde", ArcGIS_Server_Federated_with_Portal="true", Portal_Server="portal.esri.com", Portal_Username="portaladmin", Portal_Password="*****", ArcGIS_Server="ags1.esri.com", ArcGIS_Server_Port="6080", ArcGIS_Server_Username="", ArcGIS_Server_Password="", Services="Appeals.MapServer;Parcels.MapServer", Delete_Versions="", Report_Files_Directory="D:/Temp/Python", Send_E_mail_of_Results="", E_mail_From="", E_mail_To="", SMTP_Server="", SMTP_Port="")

Thank you!  Will do this tonight!

Also - on line 14, it requires the agsServer name... we have two servers who are redundant to each other.  Is there a way to include both servers so that none of the services are left on?

If these servers are part of the same ArcGIS Server site, then you will only need to specify one server. 

If they are two different servers accessing the same geodatabase, unfortunately, there is not a way to specify two servers.  There would need to be many changes to this code to do this for two different servers.

They are both configured as machines on the same ArcGIS Server Manager site.  Does that fit the "servers are part of the same ArcGIS Server site" scenario?

Yes, just specifying one server will work.  You won't have to specify both servers.

Perfect!  Thank you again!

Hello Jake,

I received an error when trying to run the script from the toolbox.  see below:

What database are using (i.e. Oracle, SQL Server, PostgreSQL)?  If you are using SQL Server, do you know if the SDE repository (the SDE tables such as sde_table_registry, sde_states, etc) are owned by SDE or DBO?

SQL database and SDE is the owner (i use the "Admin" connection when rebuilding indexes, I used the SDE connection when reconciling and compressing the database).

Thanks for sharing this great tool, Jake. Is there a particular reason you don't have rebuild indexes and update statistics as part of this tool?

I've thought about incorporating that, but with geodatabases that have multiple datasets owned by different users, it could get a little messy trying to specify all of the geodatabase connections.

That's my situation and it is a little messy. Does it matter what order you perform the two operations?

It's recommended to rebuild indexes and update stats after the compress.

Sorry, I meant if it matters if rebuild indexes runs before update stats. I understand both should happen after the compress.

Hello Jake,

I never received a response from you regarding the resolution to the error i was receiving.  You eluded to the possibility of the error occurring from the ownership of the SDE repository.  Since I currently run this process using the SDE credentials, i don't think this would be the problem.  Any other possibilities?

Blake Terhune no, this should not matter.

Marion County GIS Admin‌ I sent a response before, but it looks like it did not go through.  The script appears to be failing when trying to get the delta table counts.  You will need to debug this to see why this is occurring.  One of the best ways to do this is to add print statements (or arcpy.AddMessage since this is a GP tool).  Below is an update to the getDeltaTableCount function with additional arcpy.AddMessage statements.  This should give us an indication of where the script is getting to before it fails:

# Function to get delta table counts
def getDeltaTableCount(egdb_conn):
    arcpy.env.workspace = adminConnection

    fcList = []
    for dataset in arcpy.ListDatasets("*"):
        arcpy.AddMessage("Dataset:  " + str(dataset))
        for fc in arcpy.ListFeatureClasses("*", "", dataset):
            if arcpy.Describe(fc).isVersioned == True:
                fcList.append(fc)

    for fc in arcpy.ListFeatureClasses("*"):
        arcpy.AddMessage("Feature Class: " + str(fc))
        if arcpy.Describe(fc).isVersioned == True:
            fcList.append(fc)

    for fc in fcList:
        egdb_conn = arcpy.ArcSDESQLExecute(adminConnection)

        try:
            sqlDBO = "SELECT registration_id FROM dbo.sde_table_registry where table_name = '{0}' and owner = '{1}'".format(fc.split(".")[-1], fc.split(".")[-2])
            registrationId = egdb_conn.execute(sqlDBO)
        except:
            pass
        try:
            sqlDBO = "SELECT registration_id FROM sde.sde_table_registry where table_name = '{0}' and owner = '{1}'".format(fc.split(".")[-1], fc.split(".")[-2])
            arcpy.AddMessage("Getting registration ID")
            registrationId = egdb_conn.execute(sqlDBO)
            arcpy.AddMessage("Registration ID: " + str(registrationId))
        except:
            pass
        try:
            sqlDBO = "SELECT registration_id FROM sde.table_registry where table_name = '{0}' and owner = '{1}'".format(fc.split(".")[-1].upper(), fc.split(".")[-2].upper())
            registrationId = egdb_conn.execute(sqlDBO)
        except:
            pass


        sqlDBO = "SELECT COUNT(*) FROM {0}.a{1}".format(fc.split(".")[-2], registrationId)
        aCount = egdb_conn.execute(sqlDBO)
        arcpy.AddMessage("aCount: " + str(aCount))
        sqlDBO = "SELECT COUNT(*) FROM {0}.d{1}".format(fc.split(".")[-2], registrationId)
        dCount = egdb_conn.execute(sqlDBO)
        arcpy.AddMessage("dCount: " + str(dCount))
        try:
            versionedFeatureClasses[str(fc)].append(aCount)
            versionedFeatureClasses[str(fc)].append(dCount)
        except:
            versionedFeatureClasses[str(fc)] = [aCount, dCount]

    arcpy.AddMessage("Versioned Feature Classes: " + str(versionedFeatureClasses))
    return versionedFeatureClasses

Thank you Jake!  I'll try this today!

Hello Jake,

I tried inserting this script in place of the Get Delta Counts script in the original one and it seemed to get a bit further but then this error popped up.  Any ideas on what to do next?

Feature Class: GISDATA.GISADMIN.CENSUSBLOCKSACS2015
Feature Class: GISDATA.GISADMIN.PARCELFABRIC_LINEPOINTS
Feature Class: GISDATA.GISADMIN.PARCELFABRIC_CONTROL
Feature Class: GISDATA.GISADMIN.FLOODPRONE_AREA
Feature Class: GISDATA.GISADMIN.PARCELFABRIC_POINTS
Feature Class: GISData.GISAdmin.DrainageRetentionAreaMowingInfoView
Current State ID: 582662
Failed script CompressUpdate...

Traceback (most recent call last):
File "\\mcbcc1\dfs\GIS_IS\_Shared\Scripts\Compress GP\Compress_Database_State_0.py", line 207, in <module>
current_state_id, deltaCounts = getStateID(adminConnection)
File "\\mcbcc1\dfs\GIS_IS\_Shared\Scripts\Compress GP\Compress_Database_State_0.py", line 147, in getStateID
return state_id, deltaCounts
UnboundLocalError: local variable 'deltaCounts' referenced before assignment

Failed to execute (CompressUpdate).
Failed at Thu Aug 30 18:07:49 2018 (Elapsed Time: 2 minutes 39 seconds)

Jake,

We've recently upgraded our systems to 10.6.1 and I'm now having issues with running the compress tools.

I'm not sure why the SSL Wrong Version Number error is appearing?

Any ideas?

C:\Users\Mimiller\Source\Workspaces\HWY-GIS\RoadsAndHighways\env\Scripts\python.exe "C:/Users/Mimiller/Source/Workspaces/HWY-GIS/RoadsAndHighways/Scripts/DatabaseMaint/Compress Database State 0.py"
Current State ID:  15019
Stopping all ArcGIS Server services
     STOPing services.
Traceback (most recent call last):
  File "C:/Users/Mimiller/Source/Workspaces/HWY-GIS/RoadsAndHighways/Scripts/DatabaseMaint/Compress Database State 0.py", line 99, in <module>
    StopStartServices_Federated.Services(portalServer, agsServer, serverPort, portalUsername, portalPassword, "STOP")
  File "C:\Users\Mimiller\Source\Workspaces\HWY-GIS\RoadsAndHighways\Scripts\DatabaseMaint\StopStartServices_Federated.py", line 21, in __init__
    stop_start_Service(self.portal, self.server, self.port, self.username, self.password, self.stop_start)
  File "C:\Users\Mimiller\Source\Workspaces\HWY-GIS\RoadsAndHighways\Scripts\DatabaseMaint\StopStartServices_Federated.py", line 41, in stop_start_Service
    token = getToken(username, password, portalName)
  File "C:\Users\Mimiller\Source\Workspaces\HWY-GIS\RoadsAndHighways\Scripts\DatabaseMaint\StopStartServices_Federated.py", line 145, in getToken
    response = urllib2.urlopen(req, context=gcontext)
  File "C:\Python27\ArcGIS10.6\Lib\urllib2.py", line 154, in urlopen
    return opener.open(url, data, timeout)
  File "C:\Python27\ArcGIS10.6\Lib\urllib2.py", line 429, in open
    response = self._open(req, data)
  File "C:\Python27\ArcGIS10.6\Lib\urllib2.py", line 447, in _open
    '_open', req)
  File "C:\Python27\ArcGIS10.6\Lib\urllib2.py", line 407, in _call_chain
    result = func(*args)
  File "C:\Python27\ArcGIS10.6\Lib\urllib2.py", line 1241, in https_open
    context=self._context)
  File "C:\Python27\ArcGIS10.6\Lib\urllib2.py", line 1198, in do_open
    raise URLError(err)
urllib2.URLError: <urlopen error [SSL: WRONG_VERSION_NUMBER] wrong version number (_ssl.c:661)>

Process finished with exit code 1

Can you attach a screen shot of how you have the tool setup?  If not, are you connecting to ArcGIS Server via HTTP or HTTPs within the tool?

import arcpy, smtplib, sys, time
from arcpy import env
env.overwriteOutput = 1

# Start time
startTime = time.clock()

# Variables
# UAT connection
#adminConnection = r"\\ITDHQ1FSP07\GISServer\RoadsHighwaysScripts\Connections\SDE@RHGDB_UAT.sde"
# Prod connection
adminConnection = r"\\ITDHQ1FSP07\GISServer\RoadsHighwaysScripts\Connections\SDE@RHGDB.sde"
federatedAGS = True  # this is configured specifically for ITD's Roads and Highways setup

portalServer = 'itdhq1app114.itd.state.id.us'
#portalServer = 'itdhq1apu07.itd.state.id.us'
portalUsername = 'PortalAdmin'
portalPassword = 'XXXXXXX'

agsServer = 'itdhq1app112.itd.state.id.us'
#agsServer = 'itdhq1apu05.itd.state.id.us'
serverPort = 6443
agsUsername = ''
agsPassword = ''

deleteVersions = True
reconcileLog = r'\\ITDHQ1FSP07\GISServer\RoadsHighwaysScripts\logs\CompressTool_Log.txt'

sendEmail = True
emailFrom = 'michael.miller@itd.idaho.gov'
emailTo = ['michael.miller@itd.idaho.gov','Nicole.Hanson@itd.idaho.gov']
smtpServer = '10.1.9.67'
smtpPort = 25

# Function to get current State ID
def getStateID(adminConnection):
    egdb_conn = arcpy.ArcSDESQLExecute(adminConnection)

    sqlDBO = '''SELECT state_id FROM dbo.sde_states'''
    sqlSDE = '''SELECT state_id FROM sde.sde_states'''
    sqlORCL = '''SELECT state_id FROM sde.states'''

    try:
        state_id = egdb_conn.execute(sqlDBO)
    except:
        pass
    try:
        state_id = egdb_conn.execute(sqlSDE)
    except:
        pass
    try:
        state_id = egdb_conn.execute(sqlORCL)
    except:
        pass

    try:
        state_id = int(state_id)
    except:
        state_id = int(state_id[-1][0])

    arcpy.AddMessage("Current State ID:  " + str(state_id))
    return state_id

# Function to send e-mail
def email(Subject,Msg):

    email_Subject = Subject
    email_Msg = Msg
    email_From = emailFrom
    email_To = emailTo
    emails = ''

    # take the email list and use it to send an email to connected users.
    for email in email_To:
        emails += email + "; "
    emails = str(emails.replace(",", ";"))

    # normalize newlines to CR+LF, as required by SMTP
    headers = (('From', email_From),
               ('To', emails),
           ('Subject', email_Subject))

    body = email_Msg.replace("\r\n", "\n").replace("\n", "\r\n")

    msg = '\r\n'.join("%s: %s" % kv for kv in headers) + '\r\n'*2 + body

    # Send the mail
    server = smtplib.SMTP(host=smtpServer, port=int(smtpPort))
    server.sendmail(email_From, email_To, msg)


# Get Current State ID
current_state_id = getStateID(adminConnection)

# Stop AGS services
arcpy.AddMessage("Stopping all ArcGIS Server services")
if federatedAGS:
    import StopStartServices_Federated
    StopStartServices_Federated.Services(portalServer, agsServer, serverPort, portalUsername, portalPassword, "STOP")
else:
    import StopStartServices
    StopStartServices.Services(agsServer, serverPort, agsUsername, agsPassword, "STOP")

# Block new connections to the database.
arcpy.AddMessage("Blocking new connections to the database")
arcpy.AcceptConnections(adminConnection, False)

# Disconnect all users
arcpy.AddMessage("Disconnecting all users from database")
arcpy.DisconnectUser(adminConnection, "ALL")

# Get a list of versions to pass into the ReconcileVersions tool.
arcpy.AddMessage("Getting list of all versions")
versionList = arcpy.ListVersions(adminConnection)

for version in versionList:
    if 'default' in version.lower():
        if 'dbo' in version.lower():
            defaultVersion = 'dbo.DEFAULT'
        elif 'sde' in version.lower():
            defaultVersion = 'sde.DEFAULT'


# Execute the ReconcileVersions tool.
arcpy.AddMessage("Reconciling/posting LockRoot to Default")
if deleteVersions:
    try:
        arcpy.ReconcileVersions_management(adminConnection, "ALL_VERSIONS", "sde.DEFAULT", "ITDRH.LockRoot", "LOCK_ACQUIRED", "ABORT_CONFLICTS", "BY_OBJECT", "FAVOR_TARGET_VERSION", "POST", "KEEP_VERSION", reconcileLog)
    except:
        reconcileMessage = "Reconcile failed:  " + arcpy.GetMessages() + ".  Check reconcilelog.txt file in the " + str(reconcileLog)
        if sendEmail:
            email("Reconcile failed", reconcileMessage)
        arcpy.AddWarning(reconcileMessage)
        pass
else:
    try:
        arcpy.ReconcileVersions_management(adminConnection, "ALL_VERSIONS", "sde.DEFAULT", "ITDRH.LockRoot", "LOCK_ACQUIRED", "ABORT_CONFLICTS", "BY_OBJECT", "FAVOR_TARGET_VERSION", "POST", "KEEP_VERSION", reconcileLog)
    except:
        reconcileMessage = "Reconcile failed:  " + arcpy.GetMessages() + ".  Check reconcilelog.txt file in the " + str(reconcileLog)
        if sendEmail:
            email("Reconcile failed", reconcileMessage)
        arcpy.AddWarning(reconcileMessage)
        pass

# Run the compress tool.
arcpy.AddMessage("Running compress")
try:
    arcpy.Compress_management(adminConnection)
    arcpy.AddMessage("Compress was successful")
    compressMessageSuccess = 'True'
except Exception, e:
    compressMessage = 'Compress failed:  ' + arcpy.GetMessages() + ".  Please check the sde.COMPRESS_LOG file within the geodatabase"
    if sendEmail:
        email("Compress failed", compressMessage)
    arcpy.AddWarning(compressMessage)
    pass

arcpy.AddMessage("Rebuild indexes")
try:
    arcpy.RebuildIndexes_management(adminConnection, "SYSTEM", "", "ALL")
    arcpy.AddMessage("Rebuild was successful")
    arcpy.AddMessage("Rebuild Complete")
    rebuildMessageSuccess = 'True'
except:
        rebuildMessage = "Rebuild failed:  " + arcpy.GetMessages() + ".  Check reconcilelog.txt file in the " + str(reconcileLog)
        if sendEmail:
            email("Rebuild failed", rebuildMessage)
        arcpy.AddWarning(rebuildMessage)
        pass

arcpy.AddMessage("Analyze datasets")
try:
    arcpy.AnalyzeDatasets_management(adminConnection, "SYSTEM", "", "ANALYZE_BASE", "ANALYZE_DELTA", "ANALYZE_ARCHIVE")
    arcpy.AddMessage("Analyze was successful")
    arcpy.AddMessage("Analyze Complete")
    analyzeMessageSuccess = 'True'
except:
        analyzeMessage = "Analyze failed:  " + arcpy.GetMessages() + ".  Check reconcilelog.txt file in the " + str(reconcileLog)
        if sendEmail:
            email("Rebuild failed", analyzeMessage)
        arcpy.AddWarning(analyzeMessage)
        pass

# Allow the database to begin accepting connections again
arcpy.AddMessage("Allow users to connect to the database again")
arcpy.AcceptConnections(adminConnection, True)

# Clear workspace cache
arcpy.ClearWorkspaceCache_management()

# Start AGS services
if federatedAGS:
    arcpy.AddMessage("Starting services again")
    StopStartServices_Federated.Services(portalServer, agsServer, serverPort, portalUsername, portalPassword, "START")
else:
    import StopStartServices
    StopStartServices.Services(agsServer, serverPort, agsUsername, agsPassword, "START")

# Get New State ID
new_state_id = getStateID(adminConnection)

# Email succcessful compression results
endTime = time.clock()
elapsedTime = (endTime - startTime) / 60
message = "Previous State ID:  " + str(current_state_id) + "\nCompressed completed in " + str(elapsedTime) + " mins. \nNew State ID:  " + str(new_state_id)

if compressMessageSuccess:
    if sendEmail:
        email("Successful PROD compress", message)

Do you receive this same error when you run the GP tool?

Have not tried it as the GP tool. We have been using it just running the script. It worked before we upgraded out Roads and Highways/Workflow manager environment to 10.6.1. Out Dev/UAT environment running 10.6 works fine using the scripts.

Jake,

Why would the code behave differently between the GP tool and running script? Still perplexed as to why this will not work at 10.6.1.

Michael

Michael Miller‌ did the GP tool execute successfully?  It could be how you have your parameters set up.  After you execute the GP tool, in ArcMap go to Geoprocessing > Results > right-click on the execute tool > Copy as Python snippet.  You can paste this in an IDE or Text Editor and compare your parameters.

Thanks Jake.  All the parameters matched up.  

Something in 10.6.1 is causing it to fail. Works fine in 10.6.0.  I'll keep searching.

That is strange, I've run this tool successfully using ArcGIS Desktop 10.6.1.  Is your geodatabase upgraded to the latest version as well?  If possible, I would try another 10.6.1 geodatabase is you have one available.

Yes the database is version 10.6.1 also.  Unfortunately we do not have another 10.6.1 database feeding services with which to use for testing.

Thanks for all you assistance and suggestions.

Version history
Last update:
‎05-25-2022 11:52 AM
Updated by:
Contributors