Where should I put my script so it can run at night?

04-15-2021 08:15 AM
New Contributor II

I'm creating a geoprocessing script and was told I should make it run at night when everyone is clocked out by communicating with the database. I haven't done that before so I asked for documentation for help. My supervisor gave me the below script that would allow me to do this; however, I'm confused as to where I am supposed to put my geoprocessing script within this database connection script. Here is the script that is supposed to communicate with the database and allow my script to be run at night: 

# import packages
import os, arcpy, time, smtplib
# setup workspace and SDE databases in folder
for dirpath, dirnames, filenames in os.walk(r'//'😞
for file in dirpath:
string = r'//'
new_list = [string + x for x in filenames]
for database in new_list:
# Email users in database(s)
userList = arcpy.ListUsers(database)
# Block new connections to the database.
print('The database is no longer accepting connections.format{}'.format(database))
arcpy.AcceptConnections(database, False)
# Disconnect all users from the database.
print("Disconnecting all users")
arcpy.DisconnectUser(database, "ALL")
# Run the compress tool.
print("Running compress")
# Allow the database to begin accepting connections again
print("Allow users to connect to the database again")
arcpy.AcceptConnections(database, True)
# Rebuild indexes in database(s)
print("Rebuilding indexes on the system tables")
arcpy.RebuildIndexes_management(database, "SYSTEM")
# Updating statistics in database(s)
print('Updating statistics on the system tables.format{}'.format(database))
arcpy.AnalyzeDatasets_management(database, "SYSTEM")
print ("Finished")


I'm assuming my geoprocessing script would go after all of the code within the third for loop. 

0 Kudos
7 Replies
Occasional Contributor III

It would, as long as it is indented so as not to be included in any of those for loops.

0 Kudos
New Contributor II
Thanks for the reply. So are you saying the geoprocessing script should go
after all the above code I posted? if so, wouldn't it not have any
indentation because its going after all the for loop code blocks?
0 Kudos
MVP Regular Contributor

It depends what your geoprocessing script does. Is it part of database maintenance? That script code you posted is performing database maintenance operations that should all happen together without interference of anything else. However, the logic of that script is strange because the compress operation is in a loop but the unconditional break statement will exit the loop after the first iteration.

A better way to do it is like this where it connects as SDE to perform compress, then does analyze and indexes as owner of the objects (different database connections).

Use Python scripting to batch reconcile and post versions—ArcMap | Documentation (arcgis.com)

Additionally, I found that I needed to use arcpy.SetLogHistory(False) so as not to fill up the geodatabase metadata with this nightly task, which seemed to keep slowing down the performance of the script each time.

0 Kudos
New Contributor II

The geoprocessing script I need to add to the database script I posted just appends fields of one attribute table to another layer's attribute table, but my supervisor wants it to run at night at a given interval of time (probably once a week). 

I don't have any experience on arcpy database maintenance functions and feel timid to write my own database maintenance scripts because of my lack of knowledge in that area. Could you link some beginner friendly resources on how I could learn?

Also, for a quick solution, do you think I can take on my geoprocessing script (basically just table to table, and cursor functions) to the database script I was given, then set a schedule using task scheduler? If so, where should I put the geoprocessing script within the db script? 

Thanks for your help!

0 Kudos
MVP Regular Contributor

@GeoDev I would recommend you do not attempt to integrate your new script into this existing script for geodatabase maintenance. These should be two separate scripts run at two separate times; preferably staggered enough so one has enough time to complete before the other starts.

This is accomplished by saving your new script as a .py file and then scheduling it to run on a destkop or server machine that will be powered reliably to run this task at the scheduled time.
Scheduling a Python script to run at prescribed times—ArcMap | Documentation (arcgis.com)

New Contributor II

Ok thank you for the help. I'm very glad you told me that before I combined them. It makes sense now why they would be separate. I will do my research with the links you provided so I can better understand databases from a gis dev perspective 

0 Kudos