Home > SQL Server 2008, SQL server DBA administration, SQL Server Job Management > Monitoring multiple jobs from one Central location:SQL Server 2008 R2 Centralize server

Monitoring multiple jobs from one Central location:SQL Server 2008 R2 Centralize server

Good database administration is essential to protect critical business data and ensuring business applications operate efficiently. As a Database administrator have to perform many different tasks; many of which are reactive and some proactive, the challenge is achieving the correct balance rather than being driven with the reactive tasks.

I especially follow Monitoring multiple jobs in one central location, and receive one email each day to monitor backup and daily jobs status for 250 production server and implement the same for our existing client. . It also includes backup and recovery to ensure business continuity in the event of loss or corruption as well as ongoing availability by means of replication or some other means. Automation (of administration) can typically save 15% or more in costs by reduced administration efforts. …relieving DBAs from trivial and routine tasks…” We follow the same for our existing client

To customize the same we use the SQL Server 2008 R2 Centralize server to manage 250 remote servers as if they were a single fleet.

Set Up a Central Management Server:

To configure it, open SSMS 2008 and go into the Registered Servers window. Right-click on Central Management Servers and you get options to set one up. From there, it’s basically the same as your local registered server list – only it’s centralized:

There we can create a Group Like Production server and register all the server as below:

Once the servers are registered, you can click on the production server folder and click on the new query.

Need to execute the below script to get the job report for the entire register server.

USE msdb



SELECT job_id, MAX(instance_id) ‘Max_ID’ INTO #tempInstance

FROM sysjobhistory GROUP BY job_id

SELECT cast(@@servername as varchar(20)) as ‘server’,

j.name ‘Job_Name’,

CASE js.run_status

WHEN 0 THEN ‘Failed’

WHEN 1 THEN ‘Successful’

WHEN 3 THEN ‘Cancelled’

WHEN 4 THEN ‘In Progress’

END ‘Job_Status’,

js.run_date , sjs.next_run_date

FROM sysjobs j, sysjobhistory js, SysJobSchedules sjs, #tempInstance t

WHERE j.job_id = js.job_id AND

js.job_id = sjs.job_id AND

j.job_id = t.job_id AND

js.job_id= t.job_id AND

js.instance_id = t.Max_id AND

js.run_status IN (0) AND

j.enabled = 1 and

j.category_id = 0

DROP TABLE #tempInstance


  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: