Basic Enterprise Geodatabase Maintenance


So, you’ve got your new database set up by IT or your vendor, you create an Enterprise Geodatabase on top of it and you’re good to go! Not quite. There is a tendency for a gap to form between the maintenance tasks required for a RDBMS and a Geodatabase. Normally, the IT department will not be aware of the specific requirements of an Enterprise Geodatabase. Below are some key points to consider:

Administrative users

There is a hierarchy of users in the database. When the geodatabase is created, two users are important: the database administrators and geodatabase administrators.

Geodatabase Roles

These administrators each have their own subset of roles when it comes to doing maintenance on a database.

RoleDatabase AdminGeodatabase Admin
ResponsibilitiesThe database administrator is always created when you install the RDBMS.   Owns the database instance and databases inside it.Creates databases and schemas.Creates database users and roles.Grants database access permissions. Database-wide maintenance tasks: Backup and recovery.Configure storage in the database.  The Geodatabase administrator is created when the Enterprise Geodatabase is created/enabled on top of an RDBMS. Owns the schema.Creates users and roles.Grants table access permissions.Manages the ArcSDE configuration.Configures DBTune parameters.Perform various maintenance tasks:Post/reconcile versions.Compress.Update indexes.Update statistics.Manage the top-level version.  
WhoIT department/ RDBMS Vendor/ Esri Australia Managed Cloud SolutionsGIS Administrator
Geodatabase Administrative Roles

Some of these activities are performed ad-hoc (e.g. creating users, assigning roles), while others should be performed routinely as part of good system maintenance.

Neglecting routine geodatabase maintenance could result in degraded performance, overly-complicated lineages and excess consumption of disk space.

The below script is a Python script which performs the following routine maintenance:

  • Reconcile all versions
  • Compress the geodatabase
  • Rebuild indexes on all tables
  • Update database statistics on all tables

This script can be scheduled to run every weekend as a good start to keeping the database in good shape.

import logging
import arcpy
from datetime import date

# Set variables
#First connection should connect using the OWNER of the data. If multiple owners exist this script will need to be modified.
daConnectionString = r'D:\temp\sql_dev_db.gisowner.sde' 

#This should connect using the Geodatabase admin user.
sdeConnectionString = r'D:\temp\sql_dev_db.sde.sde'

logFileLocation = r'D:\temp\GDBMaintenanceLog'
reconcileLogFileLocation = r'D:\temp\reconcilelog_{}.txt'.format(date.today().strftime("_%d_%m_%Y"))
targetVersion = 'sde.DEFAULT'

# Setup logging
logging.basicConfig(filename=logFileLocation + date.today().strftime("_%d_%m_%Y") + '.log',
                        filemode='a',
                        format='%(asctime)s,%(msecs)d %(levelname)s %(message)s',
                        datefmt='%Y-%m-%d %H:%M:%S',
                        level=logging.INFO)

logging.info("Maintenance script starting")

try:
    # Set the workspace
    arcpy.env.workspace = sdeConnectionString

    workspace = arcpy.env.workspace
    arcpy.env.overwriteOutput = True

    logging.info("Environment set to {0}".format(sdeConnectionString))

    #Sometimes a stale connection can cause issues with versioning.
    arcpy.ClearWorkspaceCache_management()

    # Block new connections to the database.
    logging.info("The database is no longer accepting connections")
    arcpy.AcceptConnections(sdeConnectionString, False)

    # Disconnect all users from the database.
    logging.info("Disconnecting all users")
    arcpy.DisconnectUser(sdeConnectionString, "ALL")

    # Get a list of versions to pass into the ReconcileVersions tool.
    # Only reconcile versions that are children of Default
    logging.info("Compiling a list of versions to reconcile")
    verList = arcpy.da.ListVersions(sdeConnectionString)
    versionList = [ver.name for ver in verList if ver.parentVersionName == targetVersion]

    # Execute the ReconcileVersions tool.
    # We are not posting any edits to default or deleting any versions here
    logging.info("Reconciling all versions")
    arcpy.ReconcileVersions_management(sdeConnectionString, "ALL_VERSIONS", targetVersion, versionList, "LOCK_ACQUIRED",
                                       "NO_ABORT", "BY_OBJECT", "FAVOR_EDIT_VERSION", "NO_POST", "KEEP_VERSION",
                                       reconcileLogFileLocation, 'PROCEED', 'RECONCILE')

    # Run the compress tool.
    logging.info("Running compress")
    arcpy.Compress_management(sdeConnectionString)

    # Allow the database to begin accepting connections again
    logging.info("Allow users to connect to the database again")
    arcpy.AcceptConnections(sdeConnectionString, True)

    # Update statistics and indexes for the system tables
    # Note: to use the "SYSTEM" option the user must be a geodatabase or database administrator.
    # Rebuild indexes on all (including system) tables
    logging.info("Rebuilding indexes on the system tables")
    arcpy.RebuildIndexes_management(sdeConnectionString, "SYSTEM", "", "ALL")

    # Update statistics on all (including system) tables
    logging.info("Updating statistics on the system tables")
    arcpy.AnalyzeDatasets_management(sdeConnectionString, "SYSTEM", "", "ANALYZE_BASE", "ANALYZE_DELTA", "ANALYZE_ARCHIVE")

    '''
    *********************
    Data Owner(s) Section
    *********************
    '''
    # Set a variable for the workspace
    arcpy.env.workspace = daConnectionString

    # Set a variable for the workspace
    workspace = arcpy.env.workspace

    logging.info("Environment set to {0}".format(daConnectionString))

    # Get a list of datasets owned by the data owner user

    # Get the username for the workspace
    # this assumes you are using database authentication.
    # OS authentication connection files do not have a 'user' property.
    userName = arcpy.Describe(arcpy.env.workspace).connectionProperties.user
    logging.info("Connected with user {0}".format(userName))

    # Get a list of all the datasets the user has access to.
    # First, get all the stand alone tables, feature classes and raster datasets owned by the current user.
    oDataList = arcpy.ListTables('*') + arcpy.ListFeatureClasses('*') + arcpy.ListRasters('*')

    # Next, for feature datasets owned by the current user
    # get all of the feature classes and add them to the master list.
    for dataset in arcpy.ListDatasets('*'):
        oDataList += arcpy.ListFeatureClasses(feature_dataset=dataset)

    logging.info("Tables to be included {0}".format(oDataList))

    # Rebuild indexes and analyze the data owner tables
    arcpy.RebuildIndexes_management(workspace, "NO_SYSTEM", oDataList, "ALL")
    logging.info("Rebuild indexes complete")

    arcpy.AnalyzeDatasets_management(workspace, "NO_SYSTEM", oDataList, "ANALYZE_BASE", "ANALYZE_DELTA", "ANALYZE_ARCHIVE")
    logging.info("Analyze datasets complete")

    '''
    *************************
    End Data Owner(s) Section
    *************************
    '''

except Exception as e:
    print(str(e))
    print(arcpy.GetMessages(2))
    logging.error(arcpy.GetMessages(2))
    logging.error(str(e))

finally:
    logging.info("Script completed")
    print("Done.")

Input also provided by Sam Pienaar and Greg Briggs

3 thoughts on “Basic Enterprise Geodatabase Maintenance

    1. Eduard B Post author

      The daConnectionString should be the owner of the data. The sdeConnectionString should be the Geodatabase admin. In many client deployments this would be the same user (sde). GDB admin can do all the operations except rebuild indexes and recalculate statistics. This can only be done by the owner of the data. I will add some comments to the above example to clarify the connection strings.

      Reply

Got something to say?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s