none
job status across multiple servers

    Question

  • Hi Tech mates,

    I have created a backup maintenance plans in all servers in domain with same name.I have all instance name in a text file.Is there any way/scripts to retrieve the status of this job by taking the instance names from text file?

    Thanks,

    Arun



    Best Regards, Arun http://whynotsql.blogspot.com/

    Saturday, March 23, 2013 4:08 PM

Answers

  •   Step 1 - Create a SSIS package.

              Inside SSIS package

                        step 1 - import this instance names

                         step 2 - use this below query to get  the result for each instance to be in seperate table or whichever way you want.

                         
    IF object_id('TempData.dbo.JobResults','U') is not null
    		drop table TempData.dbo.JobResults
    
    DECLARE @Job_ID AS varchar(100)
    SET                @Job_ID = '%' /* you can specify a job id to query for a certain job*/ CREATE TABLE #JobResults(job_id uniqueidentifier NOT NULL, last_run_date int NOT NULL, 
                             last_run_time int NOT NULL, next_run_date int NOT NULL, next_run_time int NOT NULL, next_run_schedule_id int NOT NULL, requested_to_run int NOT NULL, 
                             /* bool*/ request_source int NOT NULL, request_source_id sysname COLLATE database_default NULL, running int NOT NULL, /* bool*/ current_step int NOT NULL, 
                             current_retry_attempt int NOT NULL, job_state int NOT NULL) INSERT #JobResults EXEC master.dbo.xp_sqlagent_enum_jobs 1, '';
                                 SELECT  
    			    r.job_id, job.name AS Job_Name,
                                                               (SELECT        TOP 1 start_execution_date
                                                                 FROM            [msdb].[dbo].[sysjobactivity]
                                                                 WHERE        job_id = r.job_id
                                                                 ORDER BY start_execution_date DESC) AS Job_Start_DateTime, cast
                                                               ((SELECT        TOP 1 ISNULL(stop_execution_date, GETDATE()) - start_execution_date
                                                                   FROM            [msdb].[dbo].[sysjobactivity]
                                                                   WHERE        job_id = r.job_id
                                                                   ORDER BY start_execution_date DESC) AS time) AS Job_Duration, r.current_step AS Current_Running_Step_ID, 
                                                           CASE WHEN r.running = 0 THEN jobinfo.last_run_outcome ELSE /*convert to the uniform status numbers (my design)*/ CASE WHEN r.job_state = 0 THEN
                                                            1 /*success*/ WHEN r.job_state = 4 THEN 1 WHEN r.job_state = 5 THEN 1 WHEN r.job_state = 1 THEN 2 /*in progress*/ WHEN r.job_state = 2 THEN 2 WHEN
                                                            r.job_state = 3 THEN 2 WHEN r.job_state = 7 THEN 2 END END AS Run_Status, 
                                                           CASE WHEN r.running = 0 THEN /* sysjobservers will give last run status, but does not know about current running jobs*/ CASE WHEN jobInfo.last_run_outcome
                                                            = 0 THEN 'Failed' WHEN jobInfo.last_run_outcome = 1 THEN 'Success' WHEN jobInfo.last_run_outcome = 3 THEN 'Canceled' ELSE 'Unknown' END /* succeeded, failed or was canceled.*/ WHEN
                                                            r.job_state = 0 THEN 'Success' WHEN r.job_state = 4 THEN 'Success' WHEN r.job_state = 5 THEN 'Success' WHEN r.job_state = 1 THEN 'In Progress' WHEN
                                                            r.job_state = 2 THEN 'In Progress' WHEN r.job_state = 3 THEN 'In Progress' WHEN r.job_state = 7 THEN 'In Progress' ELSE 'Unknown' END AS Run_Status_Description
                                   into TempData.dbo.JobResults  FROM            #JobResults AS r LEFT JOIN
                                                           msdb.dbo.sysjobservers AS jobInfo ON r.job_id = jobInfo.job_id INNER JOIN
                                                           msdb.dbo.sysjobs AS job ON r.job_id = job.job_id
                                  WHERE        cast(r.job_id AS varchar(100)) LIKE @Job_ID AND job.[enabled] = 1
                                  ORDER BY Run_Status_Description desc, Job_Start_DateTime desc 
                                                               
    
                                 DROP TABLE #JobResults

              SSRS - With the help of ssrs you can compile this result in best way - then automate the report to be sent as email whatever time you want..

                Here is one best option -- check the link .

               http://www.codeproject.com/Articles/431307/View-SQL-Server-Job-Status-using-SQL-Reporting-Ser


    Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

    Saturday, March 23, 2013 4:25 PM

All replies

  •   Step 1 - Create a SSIS package.

              Inside SSIS package

                        step 1 - import this instance names

                         step 2 - use this below query to get  the result for each instance to be in seperate table or whichever way you want.

                         
    IF object_id('TempData.dbo.JobResults','U') is not null
    		drop table TempData.dbo.JobResults
    
    DECLARE @Job_ID AS varchar(100)
    SET                @Job_ID = '%' /* you can specify a job id to query for a certain job*/ CREATE TABLE #JobResults(job_id uniqueidentifier NOT NULL, last_run_date int NOT NULL, 
                             last_run_time int NOT NULL, next_run_date int NOT NULL, next_run_time int NOT NULL, next_run_schedule_id int NOT NULL, requested_to_run int NOT NULL, 
                             /* bool*/ request_source int NOT NULL, request_source_id sysname COLLATE database_default NULL, running int NOT NULL, /* bool*/ current_step int NOT NULL, 
                             current_retry_attempt int NOT NULL, job_state int NOT NULL) INSERT #JobResults EXEC master.dbo.xp_sqlagent_enum_jobs 1, '';
                                 SELECT  
    			    r.job_id, job.name AS Job_Name,
                                                               (SELECT        TOP 1 start_execution_date
                                                                 FROM            [msdb].[dbo].[sysjobactivity]
                                                                 WHERE        job_id = r.job_id
                                                                 ORDER BY start_execution_date DESC) AS Job_Start_DateTime, cast
                                                               ((SELECT        TOP 1 ISNULL(stop_execution_date, GETDATE()) - start_execution_date
                                                                   FROM            [msdb].[dbo].[sysjobactivity]
                                                                   WHERE        job_id = r.job_id
                                                                   ORDER BY start_execution_date DESC) AS time) AS Job_Duration, r.current_step AS Current_Running_Step_ID, 
                                                           CASE WHEN r.running = 0 THEN jobinfo.last_run_outcome ELSE /*convert to the uniform status numbers (my design)*/ CASE WHEN r.job_state = 0 THEN
                                                            1 /*success*/ WHEN r.job_state = 4 THEN 1 WHEN r.job_state = 5 THEN 1 WHEN r.job_state = 1 THEN 2 /*in progress*/ WHEN r.job_state = 2 THEN 2 WHEN
                                                            r.job_state = 3 THEN 2 WHEN r.job_state = 7 THEN 2 END END AS Run_Status, 
                                                           CASE WHEN r.running = 0 THEN /* sysjobservers will give last run status, but does not know about current running jobs*/ CASE WHEN jobInfo.last_run_outcome
                                                            = 0 THEN 'Failed' WHEN jobInfo.last_run_outcome = 1 THEN 'Success' WHEN jobInfo.last_run_outcome = 3 THEN 'Canceled' ELSE 'Unknown' END /* succeeded, failed or was canceled.*/ WHEN
                                                            r.job_state = 0 THEN 'Success' WHEN r.job_state = 4 THEN 'Success' WHEN r.job_state = 5 THEN 'Success' WHEN r.job_state = 1 THEN 'In Progress' WHEN
                                                            r.job_state = 2 THEN 'In Progress' WHEN r.job_state = 3 THEN 'In Progress' WHEN r.job_state = 7 THEN 'In Progress' ELSE 'Unknown' END AS Run_Status_Description
                                   into TempData.dbo.JobResults  FROM            #JobResults AS r LEFT JOIN
                                                           msdb.dbo.sysjobservers AS jobInfo ON r.job_id = jobInfo.job_id INNER JOIN
                                                           msdb.dbo.sysjobs AS job ON r.job_id = job.job_id
                                  WHERE        cast(r.job_id AS varchar(100)) LIKE @Job_ID AND job.[enabled] = 1
                                  ORDER BY Run_Status_Description desc, Job_Start_DateTime desc 
                                                               
    
                                 DROP TABLE #JobResults

              SSRS - With the help of ssrs you can compile this result in best way - then automate the report to be sent as email whatever time you want..

                Here is one best option -- check the link .

               http://www.codeproject.com/Articles/431307/View-SQL-Server-Job-Status-using-SQL-Reporting-Ser


    Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

    Saturday, March 23, 2013 4:25 PM
  • Hi,

    I am getting the below error while trying.

    (14 row(s) affected)

    Msg 4104, Level 16, State 1, Line 9
    The multi-part identifier "jobinfo.last_run_outcome" could not be bound.

    what could be the reason?

    Thanks,

    Arun


    Best Regards, Arun http://whynotsql.blogspot.com/

    Monday, April 01, 2013 10:37 AM
  • Which version of SQL Server are you using?

    Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

    Tuesday, April 02, 2013 3:10 PM
  • 2008 R2 SP1

    Best Regards, Arun http://whynotsql.blogspot.com/

    Tuesday, April 02, 2013 4:10 PM
  • Can you see what is the result you are getting seperately when you run this below select statment?

    select * from #JobResults


    Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

    Tuesday, April 02, 2013 4:47 PM
  • it says invalid table.table is not getting created


    Best Regards, Arun http://whynotsql.blogspot.com/

    Tuesday, April 02, 2013 6:23 PM
  • CREATE TABLE #JobResults
    (
    job_id uniqueidentifier NOT NULL, 
    last_run_date int NOT NULL, 
    last_run_time int NOT NULL, 
    next_run_date int NOT NULL, 
    next_run_time int NOT NULL, 
    next_run_schedule_id int NOT NULL, 
    requested_to_run int NOT NULL, 
                             /* bool*/ 
    request_source int NOT NULL, 
    request_source_id sysname COLLATE database_default NULL, 
    running int NOT NULL, /* bool*/ 
    current_step int NOT NULL, 
    current_retry_attempt int NOT NULL, 
    job_state int NOT NULL)
    
    
    INSERT #JobResults EXEC master.dbo.xp_sqlagent_enum_jobs 1, '';

    Try this above.

    After that you will be able to select #jobResults.

    Then you can validate on "r.running " column. Check out what kind of status you are able to view.


    Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

    Tuesday, April 02, 2013 7:55 PM