The Nightly Script That Fails Two Nights a Week
A transit agency's asset team runs a nightly Python 3 script under Windows Task Scheduler at 02:00 via the ArcGIS Pro Python environment. It copies a vendor CSV from a file share, joins it to TrackAsset in an enterprise geodatabase, recalculates LastInspect and Condition, and writes a run summary. Three weeks ago the schedule began failing two or three nights a week with 'ERROR 000464: Cannot get exclusive schema lock. Either the file is in use or you do not have permission.' On nights it does run, the last step fails with 'TypeError: a bytes-like object is required, not str' while writing the summary file. The task runs as a service account signed in to the portal once, months ago. When it succeeds the run takes 70 minutes; when an analyst runs the identical script from her own Pro session it takes 11 minutes. The vendor CSV occasionally contains duplicate asset IDs.
1. Diagnose the intermittent schema lock error and give a remediation plan that does not depend on someone remembering to close Pro.
Read the worked answer
Who holds the lock. In an enterprise geodatabase the usual holders, in order of likelihood: the published feature service whose data store connection has been queried or edited since the last restart; an analyst's Pro session left open overnight with the layer in a map; and the script itself, if it leaves a cursor or a layer with a retained join open across writes. A previous run that hung and never exited is the most common explanation for 'fails two or three nights a week' rather than every night.
Prove it, do not guess. Log the process id and timestamp, then at the moment of failure query the RDBMS session and lock views, or the geodatabase's connected-user listing, and record which session blocks the dataset. Confirm whether an orphaned process from the prior run is still alive.
Remediate structurally.
- Write into a staging table in the same geodatabase, then apply to the published dataset in one short transaction.
- If the dependent service must be stopped, do it inside a documented window and restart it in a finally block.
- Release every resource explicitly: delete cursors, remove joins, delete temporary layers, clear the workspace cache.
- Wrap the run in try/finally with a nonzero exit code so the scheduler alert actually fires, and add a lock check at step one that fails fast with a clear message.
2. The scheduled run takes 70 minutes while the analyst's identical run takes 11. Explain the likely causes and how you would investigate and fix them.
Read the worked answer
Assume environment before code, but verify. The script is the same; the surroundings are not. Candidates ranked: the 02:00 window collides with database backup and index maintenance or other nightly jobs, so writes block and retry; the service account's connection file differs from the analyst's, for example a different instance or a connection type that routes through an application server; row-by-row cursor logic is doing what set-based SQL should do; and the analyst's session benefits from warm metadata caches and, on some nights, runs after a colleague already did the heavy step.
Investigate with timers, not intuition. Instrument each stage with elapsed time, log row counts at each step, and capture blocking information from the database during the scheduled run. Compare the two runs' connection properties and Python interpreter paths.
The service account also needs a valid license. Signing in once months ago is not a durable state; a named-user token expires. Either have the script sign in explicitly with a stored credential before it touches licensed tools, or configure the machine with a license that does not expire, and confirm the account's license level includes any extensions the workflow calls. Check licensing first so a licensing failure reports as a licensing failure instead of surfacing later as an unrelated lock error.
Then fix the hot spot: staging table, set-based update, indexes on the join key, and no network calls inside an open cursor.
3. Explain the TypeError, what else in the script is likely to break for the same reason, and how the duplicate asset IDs should be handled.
Read the worked answer
The TypeError is a Python 2 habit. Writing a string to a file opened in binary mode was tolerated when str meant bytes; Python 3 raises 'a bytes-like object is required, not str'. Open the summary in text mode with an explicit encoding and a fixed newline, or encode before writing. Then audit the rest of the migration in the same pass: arcpy.mapping routines must become arcpy.mp, has_key becomes in, integer division changes results silently, and every CalculateField call must declare the Python 3 expression type. A Python 2 style expression still executes but changes string and date handling, which is the kind of failure that shows up as wrong values rather than a traceback.
Duplicates are worse than a crash because they fail quietly. A one-to-many join makes the recalculation take whichever matching row the cursor touched last, so a condition value can come from the wrong inspection and the summary counts inflate. Detect duplicates before the join, either by counting keys in memory or with a Find Identical style comparison on the key. Then apply one explicit rule, such as most recent inspection timestamp wins, write rejected rows to a QC table with the reason, and log the counts. Have the script exit nonzero when duplicates exceed an agreed threshold so the scheduler's failure notification is the thing that raises the alarm, not an inspector in the field.