You need to maintain your SDE database. You just need to do it. Its more important when you have more edits, versions, states, data, etc. But everyone should do it regularly regardless.
There are a million ways to make this happen. I sat down and came up with a simple way to do simple maintenance in an automated fashion that would benefit most people.
The process is as follows:
- Install ArcGIS Pro on a server machine
- Create and test a maintenance python script there
- Add the script to Task Scheduler
Pretty simple 1-2-3, I think. Installing ArcGIS Pro on the server machine gives you the python environment with all the Esri python packages we need to access the SDE database and run the various tools. It also gives you the authorization to run them.
The server machine is presumably behaving like a normal server, running all the time and ready to go when the task is set to run each day, week, or month—however, you choose to configure it.
Step 1
Let’s start with step one. Install ArcGIS Pro on the server machine and log in or authenticate however you do that at your organization.
Step 2
Let’s create our script. Esri has some great documentation on the whole process recommended for database maintenance. I used some of their sample code, simplified some things, and added some nice timestamped print statements to get a nice log of progress as the script runs.
I skipped adding a logger, handling errors in a graceful way, or doing anything else too fancy to make this as simple as possible. I simply wrapped the bulk of the code in a try loop so that if it fails somewhere in the middle it will set the SDE database back to allowing connections.
If you want to just run this script as-is, there is only one parameter that needs to be addressed.
On the 4th line, either replicate the path of this SDE file (C:\temp\mydatabaseconnection.sde) on your machine or alter it in the script to match your SDE file. We are simply looking for the location of an SDE file you create in ArcGIS Pro with stored credentials.
The database user you set in this SDE file must have the database role of “process admin” which you can set in something like SQL Server Management Studio if you have high enough permissions. Make sure you check that box after typing in the username and password so it stores them.
You can find the location of this SDE file in the properties by right-clicking and using the context menu. Go to that location and copy the SDE file to where you want to store it to run from Task Scheduler. The location in the script is “C:\temp\mydatabaseconnection.sde”.
import arcpy
import time
try:
sde_connection = r"C:\temp\mydatabaseconnection.sde"
arcpy.env.workspace = sde_connection
arcpy.env.overwriteOutput = True
db_name = arcpy.Describe(sde_connection).connectionProperties.database
except:
print("[{0}] *** Script encountered an error. Cancelling operation. ***".format(time.ctime()))
print("[{0}] Finished maintenance.".format(time.ctime()))
try:
print("[{0}] Starting maintenance...".format(time.ctime()))
print("[{0}] Blocking connections to '"'{1}'"'.".format(time.ctime(), db_name))
arcpy.AcceptConnections(sde_connection, False)
print("[{0}] Disconnecting all users from '"'{1}'"'.".format(time.ctime(), db_name))
arcpy.DisconnectUser(sde_connection, "ALL")
versionList = arcpy.ListVersions(sde_connection)
print("[{0}] Starting reconcile on {1} version(s)...".format(time.ctime(), len(versionList)))
arcpy.ReconcileVersions_management(sde_connection, "ALL_VERSIONS", "sde.DEFAULT", versionList,
"LOCK_ACQUIRED", "NO_ABORT", "BY_OBJECT", "FAVOR_TARGET_VERSION",
"POST", "DELETE_VERSION")
print("[{0}] Finished reconcile.".format(time.ctime()))
print("[{0}] Starting database compress...".format(time.ctime()))
arcpy.Compress_management(sde_connection)
print("[{0}] Finished database compress.".format(time.ctime()))
clog = arcpy.ListTables("*SDE_compress_log","")
countfield = "end_state_count"
lastcount = [r[0] for r in arcpy.da.SearchCursor(clog[0], countfield)][-1]
print("[{0}] Found {1} states after compress.".format(time.ctime(), lastcount))
print("[{0}] Allowing connections to '"'{1}'"'.".format(time.ctime(), db_name))
arcpy.AcceptConnections(sde_connection, True)
userName = arcpy.Describe(sde_connection).connectionProperties.user
dataList = arcpy.ListTables('*.' + userName + '.*') + arcpy.ListFeatureClasses('*.' + userName + '.*') + arcpy.ListRasters('*.' + userName + '.*')
for dataset in arcpy.ListDatasets('*.' + userName + '.*'):
dataList += arcpy.ListFeatureClasses(feature_dataset=dataset)
print("[{0}] Starting rebuild indexes...".format(time.ctime()))
arcpy.RebuildIndexes_management(sde_connection, "NO_SYSTEM", dataList, "ALL")
print("[{0}] Finished rebuild indexes.".format(time.ctime()))
print("[{0}] Starting analyze datasets...".format(time.ctime()))
arcpy.AnalyzeDatasets_management(sde_connection, "NO_SYSTEM", dataList, "ANALYZE_BASE",
"ANALYZE_DELTA", "ANALYZE_ARCHIVE")
print("[{0}] Finished analyze datasets.".format(time.ctime()))
print("[{0}] Finished maintenance.".format(time.ctime()))
except:
print("[{0}] *** Script encountered an error. Cancelling operation. ***".format(time.ctime()))
print("[{0}] Allowing connections to '"'{1}'"'.".format(time.ctime(), db_name))
arcpy.AcceptConnections(sde_connection, True)
print("[{0}] Finished maintenance.".format(time.ctime()))
Its almost time to test the script in your environment. Pay attention here. The script attempts to do the following:
- Block database connections
- Disconnect users who are connected
- Reconcile versions
- Post versions
- Delete versions
- Compress the database
- Allow database connections
- Rebuild indexes
- Analyze statistics
Obviously, this can cause disruptions. There are conditions where versions will not be deleted, states will not get compressed, users will not get disconnected. But it tries to and might do all these things. It is your responsibility to be aware of this. Make sure if you set this to run your users are aware. If you schedule it for Friday night at 11 PM tell them it’s “do or die” regarding their versions, edits, connections, etc. They need to be done before then. And then tell them to go home because it’s Friday at 11 PM.
With that out of the way, replicate or change the SDE path parameter in line 4, open up Jupyter Notebook, the ArcGIS Pro console, or the Python Command Prompt and let it rip.
Once you are satisfied that it works as it sits save it as a PY (python) file and place it where you want it to live.
In my example here I made a folder on the C drive called “db_maint” and copied a PY file called “db_maint.py” there which contains the script.
Jot this python file path down!
Next, find the python.exe file your ArcGIS Pro’s python environment is using by looking at Settings > Python > Project Environment.
If you navigate to that path in File Explorer, you will find the python.exe. This is the python interpreter that will be used when you open the Python Command Prompt or Jupyter Notebooks installed by ArcGIS Pro on the machine.
Jot this EXE path down too, we need it in a minute!
Step 3
Finally, we get to step 3. Open Task Scheduler right on this same server machine with ArcGIS Pro that you just installed.
Create a new simple task. Add a name, add a trigger schedule, etc. The last piece here is to fill out two inputs in the action details.
The “Program/script:” input gets the path of the python.exe you noted above. My path looked like this:
“C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\python.exe”
The “Add arguments (optional):” input gets the path to your PY file. My path looked like this:
“C:\db_maint\db_maint.py”
Hit OK and OK and maybe another OK.
You can test the task by right-clicking on it at the top level of Task Scheduler and hitting Run. But again, be sure you know what it’s doing and that you are good to run it.
And that is it. Your maintenance script will run when you told it to. Like magic. Very nerdy magic. This is a pretty nifty pattern to follow. You can create and run different python scripts to do different things in the same way.
You can do the same kind of thing with ArcGIS Notebook Server which gives you a method to task python scripts and run them, but a lot of organizations don’t have that infrastructure set up yet or have decided not to include it in their Enterprise stack. So this is a decent solution when you want a very simple automated SDE database maintenance process to make your life easier.
What do you think?