locked
Running set of SQL agent jobs in parallel RRS feed

  • Question

  • Hi,

    I am able to run a sqlagent job from powershell. Can anyone suggest me how to run multiple sql jobs in parallel and wait for the set to get complete. Once all jobs in first set completes, next set of sql jobs should start.

    Lets say..

    Set1 = Job1.1, Job1.2, Job1.3

    Set2 = Job2.1, Job2.2

    Set 3 = Job3.1, Job 3.2, Job 3.3, Job 3.4

    My PS should start all jobs {Job1.1, Job1.2, Job1.3} in parallel, wait for all jobs to complete. If any of them failed, Rerun them.. and wait for completion...

    Next, Set 2 will be started..


    sekhar


    • Edited by RAVISANGAM Friday, April 12, 2019 7:54 AM
    Friday, April 12, 2019 6:47 AM

All replies

  • FYI..

    I know how to run SQL job from Powershell... I need ideas, suggestions to implement a solution for my problem..

    Do you know how to achieve it?


    sekhar

    Friday, April 12, 2019 7:51 AM
  • We do not provide free consulting.  THis forum is for questions about a script you have written.

    Your question, as asked,  is not a scripting question.

    Please see this: How to ask   questions in a technical forum


    \_(ツ)_/

    Friday, April 12, 2019 7:54 AM
  • If you are trying to ask how to run parallel scripts then please read the following; https://docs.microsoft.com/en-us/powershell/module/psworkflow/about/about_workflows?view=powershell-5.1



    \_(ツ)_/

    Friday, April 12, 2019 7:56 AM
  • We bought Microsoft products. I don't know how to run sql jobs in parallel .. so asked a question. You want me to post my code here?



    sekhar

    Friday, April 12, 2019 7:58 AM
  • Please understand that this is not a Microsoft Support forum.  Please read the links I posted.  They will help you to understand why your question cannot be answered.  The "workflow" link will show you how to write a workflow.

    There is no way for us to help you with this because your question is too vague.

    If you want MS support help then post here: http://support.microsoft.com

    You can also contact a trained consultant to help you understand how to do what you ask.  You will need to design a solution using standard programming design methods.  Once you have a complete description of the problem then a script/program can be designed.

    You can also create tasks o n SQLServer that can run parallel jobs.  Post in SQLServer forum to get help with designing parallel tasks in SQLServer.

    I also recommend using help to search for tutorials on designing and building tasks in SQLServer:

    This will show you how to create multi-step jobs.

    https://www.mssqltips.com/sqlservertip/4920/creating-multistep-and-dynamic-sql-server-agent-jobs/

    The following will show you the different ways to create multi-step jobs using SMO:

    https://docs.microsoft.com/en-us/sql/ssms/agent/create-a-transact-sql-job-step?view=sql-server-2017


    \_(ツ)_/

    Friday, April 12, 2019 8:10 AM
  • The $SqlServer variable is not filing..                                                     

       

         
    WORKFLOW RunJobs
        {
          param
          (
            [Parameter(Mandatory=$True)][System.Xml.XmlNode[]] $SqlJobs,
            [Parameter(Mandatory=$True)][String]               $DatabaseInstance
          )
          
          InlineScript
          {
            # Load SMO and instantiate the server object
            [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |Out-Null;
            [Microsoft.SqlServer.Management.Smo.Server]$sqlServer = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $DatabaseInstance;
          }
     
          #loop through the list of jobs
          foreach -parallel ( $SqlJob in $SqlJobs )
          {       
            [Microsoft.SqlServer.Management.Smo.Agent.Job]$job = ($sqlServer.JobServer.Jobs | ? { $_.Name -eq $SqlJob.Name });
                
            InlineScript
            {
                $job.Start();
            }
            $jobStartTime = Get-Date
    
            Start-Sleep -s 1
     
            InlineScript
            {
                Write-Host "$Job Started: $jobStartTime"
            }
            $jobStatus = $job.CurrentRunStatus
            while($jobStatus -ne 'Idle')
            {
              InlineScript {$job.Refresh()} #refresh the job connection
              $jobStatus = $job.CurrentRunStatus #check the job's status
            }
     
            $jobEndTime = Get-Date
            $jobResult = $job.LastRunOutcome 
    
            InlineScript
            {
                Write-Host "$Job Completed with a Status of $jobResult : $jobEndTime" 
                if ($jobResult -ne 'Succeeded') {break}
            }       
          }   
    }



    sekhar




    • Edited by RAVISANGAM Friday, April 12, 2019 8:22 AM
    Friday, April 12, 2019 8:12 AM
  • I don't understand what "filing" means.  It is not a term.

    Please post code correctly so it is readable using the code posting tool provided. 

    Please rad the following before continuing: How to ask   questions in a technical forum

    If you cannot be clear you will have to find a consultant or someone in your organization who can understand your language.


    \_(ツ)_/

    Friday, April 12, 2019 8:16 AM
  • Come onnnn..

    This forum is not the right one.. thats what you are trying to say... But there are many questions which are answered in this forum.. anyway.. thanks.... I will search in other USEFUL forums.. It would be nice if microsoft can provide a platform for users to discuss their problems with NO ADMINSSSS..  Posting here is just waste of time..


    sekhar

    Friday, April 12, 2019 8:24 AM
  • Come onnnn..

    This forum is not the right one.. thats what you are trying to say... But there are many questions which are answered in this forum.. anyway.. thanks.... I will search in other USEFUL forums.. It would be nice if microsoft can provide a platform for users to discuss their problems with NO ADMINSSSS..  Posting here is just waste of time..


    sekhar

    I am sorry that you cannot understand and cannot understand how to ask a technical question.  I recommend hiring a trained consultant to work with you to define what your needs are.

    As noted above, this is not a free consulting forum.  You have not asked a question that can be answered any better than I have tried to answer it.  If you were better able to understand the way to ask your question then we might be able to help.

    Example - you posted this line "The $SqlServer variable is not filing." which makes no sense and there is no variable $SqlServer anywhere in the script.

    Good luck.  Perhaps you will find a consultant.


    \_(ツ)_/

    Friday, April 12, 2019 8:41 AM
  • IF anyone wants answers for your powershell related queries, please post in the below forum.. The admins of this forum are really helpful..

    https://community.idera.com/database-tools/powershell/ask_the_experts/f/sql_server__sharepoint-9


    sekhar

    • Marked as answer by RAVISANGAM Friday, April 12, 2019 8:58 AM
    • Unmarked as answer by jrv Friday, April 12, 2019 9:17 AM
    Friday, April 12, 2019 8:58 AM