Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Posts Tagged ‘JOB’

SQL Agent Job server.(Centralized job server)

Posted by Simon Cho on 07/03/2014


Automated Administration Across an Enterprise


Automating administration across multiple instances of SQL Server is called multiserver administration. Use multiserver administration to do the following:

  • Manage two or more servers.
  • Schedule information flows between enterprise servers for data warehousing.


To take advantage of multiserver administration, you must have at least one master server and at least one target server. A master server distributes jobs to, and receives events from, target servers. A master server also stores the central copy of job definitions for jobs that are run on target servers. Target servers connect periodically to the master server to update their schedule of jobs. If a new job exists on the master server, the target server downloads the job. After the target server completes the job, it reconnects to the master server and reports the status of the job.

The following illustration shows the relationship between master and target servers:

Multiserver administration configuration

If you administer departmental servers across a large corporation, you can define the following:

  • One backup job with job steps.
  • Operators to notify in case of backup failure.
  • An execution schedule for the backup job.


Posted in Common | Tagged: , , , , | Leave a Comment »

Find out sql job start time and end time

Posted by Simon Cho on 06/05/2012

Since, msdb.dbo.sysjobhistory has run_duration as INT data type.
Need to convert to find out actual job start time and end time.

create function [dbo].[fn_get_job_start_datetime] 
(@run_date int, @run_time int)
returns datetime
	return convert(datetime,rtrim(@run_date)) 
			+ (@run_time*9 
				+ @run_time%10000*6 
				+ @run_time%100*10) / 216e4

create function [dbo].[fn_get_job_end_datetime] 
(@run_date int, @run_time int, @run_duration int)
returns datetime
	if @run_duration<0  -- some of replication job has minus(-) value.
		set @run_duration = 99999999
	return convert(datetime,rtrim(@run_date)) 
			+ (@run_time*9 
				+ @run_time%10000*6 
				+ 25*@run_duration) / 216e4

select top 10 as job_name
	 , h.run_date
	 , h.run_time
	 , h.run_duration
	 , h.run_status
	 , dbo.fn_get_job_start_datetime(h.run_date, h.run_time) 
		as job_start_datetime
	 , dbo.fn_get_job_end_datetime(h.run_date, h.run_time, h.run_duration) 
		as job_end_datetime
  from msdb.dbo.sysjobs j
  join msdb.dbo.sysjobhistory h on j.job_id = h.job_id

Posted in Common | Tagged: , | Leave a Comment »