Subtasks from SQL Server Agent are loosing their parent relation

• bjoernB

  SSC Journeyman

  Points: 75

  More actions

  January 19, 2018 at 3:44 am

  #334620

  Hey there,

  first of all, I feel like I have to do a little disclaimer before I will get to my point. The coming problem is by no means "best practice" and I would build this "structure" / "architecture" completely different.

  Enough said, lets get straight to my problem.

  In my scenario, I've got multiple SQL Servers and one of them got certain problems. To simplify the description, I introduce you to two systems.

  Production -> Microsoft SQL Server 2014 (SP2-GDR) (KB3194714) - 12.0.5203.0 (X64)

  Staging -> Microsoft SQL Server 2014 (SP2-CU6) (KB4019094) - 12.0.5553.0 (X64)

  Both running at different V-Server with Microsoft Server 2012 R2 (two servers / each with only one instance of sql server)

  On both SQL Servers, there are multiple jobs, who do the same stuff. These jobs are pretty much the same, only some parameters are different.

  In my case, there are multiple jobs of type "Operating system (CmdExec)" who got just one step: Starting "runSomeStuff.exe".

  On my production environment, everything works fine and when I check my taskmanager / process explorer, this will looks like this:

  SQLAGENT.exe

  • runSomeStuff.exe
  • runSomeStuff.exe
    
  • runSomeStuff.exe
    
  • runSomeStuff.exe
    
  • runSomeStuff.exe
    
  • runSomeStuff.exe
    

  When I check one of these "runSomeStuff.exe", the info-message shows the correct parent-process (which is SQLAGENT.exe)

  Sometimes on my staging-environment, I encounter a strange behavior: Long Running Jobs. When I check my staging system, the process explorer will show something like this:

  SQLAGENT.exe

  • runSomeStuff.exe
  • runSomeStuff.exe
  • runSomeStuff.exe

  runSomeStuff.exe
  runSomeStuff.exe
  runSomeStuff.exe

  As you can see (when the formatting is not doing a horrible job and your imagination will allow it 🙂 ) some of these "runSomeStuff.exe" are running without any parent relations to the sqlserveragent.exe. This is a problem.

  Now these Jobs are running forever.

  When I check some information from these "headless" processes, I can see "parent: <Non-existent Process>(1234)"

  I've got these Information not in the Task-Manager, I found them via "Process Explorer" - A tool from microsoft's system-internal tools.

  I try to ask the developers from "runSomeStuff.exe" about this behavior, but they cant help. (It looks to me like a Windows or Sql Server Agent issue too)

  Now, before I risk some downtime for my "staging" system, I like to ask some of you experienced guys for advice or if any of you may encounter similar issues.

  I'm willing to update my staging system to a newer patch level for the sql server 2014, but I cannot upgrade this instance to a newer version of the sql server. Unfortunately, I can't change that the SQL Server Agent is used as a job controller for the applications.

  I would be happy with any clues you throw at me.

  Best Regards, 
  Bjoern