Access to Geodatabase table using python randomly denied.

Question

I have several scripts that are run against a geodatabase, each script creates a sde file with the credentials, then proceeds to read a value from a table using a search cursor

database = gp.create_connectionfile(log, base_dir, user_do, instance_sde, "DATABASE_AUTH",
                                        username=user_do, password=password_do, save_user_pass="SAVE_USERNAME",
                                        database=database_name)
    arcpy.env.workspace = database

# Retrieve and strip current version
    source_version = None
    with arcpy.da.SearchCursor("{0}.RAC_ADD_IN".format(user_do), "VERSION") as cursor:
        for row in cursor:
            if row[0]:
                source_version = StrictVersion(row[0].rsplit(".", 1)[0])
            break

When the set of scripts run, sometimes all scripts run without issue, sometimes one script fails, the issue is that the failing script is not always the same. the error presented is:

ERROR cannot open 'DataOwner.RAC_ADD_IN'
 ERROR ['Traceback (most recent call last):
', '  File "C:\01_Script.py", line 134, in <module>
    main(argv[1:])
', '  File "C:\01_Script.py", line 86, in main
    with arcpy.da.SearchCursor("{0}.RAC_ADD_IN".format(user_do), "VERSION") as cursor:
', "RuntimeError: cannot open 'DataOwner.RAC_ADD_IN'
"]

Facts:

* The credential are always the same
* The table is always the same
* In the script above, the line 1 is just a wrapper to the arcpy function to create a sde connection file. 
* This behavior has been appearing since ArcGIS Pro 2.8, currently this is running in 2.9.1

How this can be managed?

DonMorrison1 · Answer

Sorry that was bad advice. I meant the ArcGIS Server Manager but that is irrelevant in your scenario.  Maybe you could add code to retry the connection a failed connection a number of times?  Is there something in your any of your scripts that would temporarily lock the database and prevent connections?

Cristian_Galindo · Answer

do you mean to change configuration for the logging process in the SQL server?

Cristian_Galindo · Answer

As is is already state in the post:

"In the script above, the line 1 is just a wrapper to the arcpy function to create a sde connection file. "

but for the sake of clarity, the implementation of the method is:

def create_connectionfile(self, logger, out_folder_path, out_name, instance, account_authentication=None, username=None, password=None, save_user_pass=None,
                              database=None, schema=None, version_type=None, version=None, date=None):
        try:
            conn_file = "{0}.sde".format(os.path.join(out_folder_path, out_name))
            logger.add("Starting process to create connectionfile {0}".format(conn_file))
            if os.path.exists(conn_file):
                os.remove(conn_file)
                logger.add("Existing connectionfile {0} is removed".format(conn_file))

arcpy.CreateDatabaseConnection_management(out_folder_path=out_folder_path,
                                                      out_name=out_name,
                                                      database_platform="SQL_Server",
                                                      instance=instance,
                                                      account_authentication=account_authentication,
                                                      username=username,
                                                      password=password,
                                                      save_user_pass=save_user_pass,
                                                      database=database,
                                                      schema=schema,
                                                      version_type=version_type,
                                                      version=version,
                                                      date=date)
            logger.add("Connection file {0} is created".format(conn_file))

return conn_file
        except Exception as e:
            logger.add("Create_connection file has failed: {0}".format(e), "ERROR")