Dynamically capturing Map Service name and associated PID into a SQL Table

Blog Post created by micsbjc on Sep 9, 2015


In my first post I described how you can associate a SQL connection back to its source MapService name.

This post will describe a technique for capturing this information dynamically into a SQL table so that you can execute SQL queries against SQL system tables and relate them to specific ESRI Map services.



  • Before you start, review the supplied decks so that you understand what the processing is and the risks involved. Do not implement on your live environment without first testing in a non-critical environment
  • Decide on location for SQL Table
    • Name             Default Value
    • Instance         SQLInstance
    • Database       DBA_Monitoring
    • Schema         ESRI
  • Edit CreateSQLEnvironment.SQL deck and adjust Database and Schema as appropriate and then run
    • This deck will
      • Create a table to hold the current status of each ESRI process
      • Create stored procedures to interface from Capture process
  • On each ArcGIS server you need to do the following..
    • Decide on a folder location to anchor this process
    • Edit TrackArcSOC.PS1 and adjust Instance, database and schema
    • Save TrackArcSOC.PS1 to the chosen Folder location
    • Create a subfolder called EXPORT in chosen Folder location. TrackArcSOC will create a log file in this folder for each invocation.
    • Create a scheduled task on each ArcGIS Server to invoke TrackArcSOC.PS1 after each reboot
      • The scheduled task needs the following characteristics
        • A username that
          • Is a member of the local administrators group (so that WMI will return Commandline information)
          • Has execute access on the stored procedures created with CreateSQLEnvironment.SQL deck
        • Run whether user is logged on or not
        • Trigger of ‘Begin Task at startup’ with a delay of 15 minutes (to allow WMI to settle down before invocation)
        • Action of
          • Powershell.exe –file <chosen folder location>\ TrackArcSOC.PS1
    • Invoke scheduled task
    • If all goes well then the target table should contain one row for each active ArcSOC instance.



My next post will describe how you can use this table to provide information on the status of your ESRI environment.