1

We have a requirement to design some sort of reporting the status of SQL agent job status for all the sql servers in the org.

I am able to do it via querying dmvs in msdb directly for that instance but it holds true only for one server at a time. Meaning if i create an SSRS report, i can filter based on instance name.

However the requirement we have that all sql instances, approx 50 should show under a common repository from where we can see the status. For example we can see job failed on how many servers if one wants to view at a given time.

I am not sure how real time this would be compared to direct querying but any idea how can this be achieved or any solution already available that i can use or customize?

Thanks

3 Answers 3

0

This is an older post but if anyone needs centralized monitoring for your SQL Agent jobs, DBA Dash will help. It's a free & open source monitoring tool that has great support for monitoring SQL Agent jobs.

  • The Summary page has an overall status across all your instances on a single dashboard - Including a load of other daily checks.
  • Drill down to see which jobs have failed and why
  • Track the performance of your agent jobs over time
  • Track long-running jobs
  • Timeline view of your jobs
  • Track DDL checks to your agent jobs.
1

Welp, from a purely SQL Server perspective you have two options that are actually real-time:

  1. You pick a server as the centralized point of querying, and setup a Linked Server on it for every other SQL Server you care about. Then you can query whatever you need from the other servers via a single server instance / single connection string.

  2. In SSRS, you setup a data source in your reports project for each SQL Server instance you want to report on. Then you can setup datasets that UNION the results of each data source, to be consumed by your reports.

Both options sound excessive to me, to be honest. Alternatively, you can either schedule your own SQL Job or setup some form of Replication on each server (this seems even more excessive, maybe don't do this) to synchronize the data to a single server, at a tolerable frequency to your business. Or you can write your own application service to do this for you.

I'd probably either lean towards creating a SQL Job or setting up the Linked Servers myself.

7
  • 1
    Instead of linked servers (50+?) and as an option: a cycle with set of servers + dynamic sql + openrowset Commented Aug 31, 2022 at 16:35
  • @AndreySamykin That sounds like an interestingly good idea. 🤔 Maybe you want to post an answer with some examples?...I think I know what you mean.
    – J.D.
    Commented Aug 31, 2022 at 16:48
  • sure, here it goes Commented Aug 31, 2022 at 17:32
  • Rather than linked servers, i'd use SSIS to poll each server, or as a stored job to push data to a central point. Commented Aug 31, 2022 at 23:32
  • @SirSwears-a-lot Like a SQL Job, in each server?...because that would generally require just as many Linked Servers (just all for the same endpoint server and created in each source server). SSIS I'm not particularly a fan of for basic tasks. I do like Andrey's idea of leveraging Dynamic SQL for doing the heavy lifting of generating the appropriate scripts though.
    – J.D.
    Commented Sep 1, 2022 at 0:16
0

As J.D. suggested, the following code could be used if there are no any restrictions or policies:

set nocount on;
declare @servers table (srv sysname)
declare @jobs table (srv sysname,jobname sysname,rundatetime datetime,run_status int,message_text nvarchar(1024))
declare @cmd1 nvarchar(512)
declare @cmd2 nvarchar(512)
declare @sql sysname

insert into @servers (srv)
select 'SQL1' union all
select 'SQL2' union all
select 'SQL3' 

set @cmd1='select * from OPENROWSET(''SQLNCLI'', ''Server=%s;Trusted_Connection=yes;'', ''select @@servername [sql], j.name, msdb.dbo.agent_datetime(run_date, run_time) [RunDateTime], jh.run_status, jh.message from msdb..sysjobhistory jh join msdb..sysjobs j on j.job_id=jh.job_id where jh.step_id=0 and jh.run_date=convert(int,convert(varchar(10),getdate(),112))'')'

while exists (select top 1 1 from @servers)
    begin

        select top 1 @sql=srv 
        from @servers

        print @sql

        set @cmd2=FORMATMESSAGE(@cmd1,@sql)

        insert into @jobs (srv,jobname,rundatetime,run_status,message_text)
        exec (@cmd2)

        delete @servers 
        where srv=@sql

    end

select * from @jobs 

This code queries msdb sysjobs tables and gets job execution stats for today from three servers SQL1, SQL2, SQL3. The query is just a sample and can be modified and enhanced in accordance with your needs.

Sample output: Output of the script

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.