July 20, 2016 at 3:03 pm
We have 100's of remote server and wanted to see if there is a way i can run query on my desktop which will fetch data from all the servers (remote) and give me following:
What time my system db backup job started and end?
What time my user db backup job started and end?
please let me know
thanks
July 21, 2016 at 2:11 am
Hi
A couple of suggestions:
(1) use the Registered Servers functionality in SQL Server Management Studio to create a group of servers that you wish to query, and run the query against all the servers at the same time.
(2) Use an SSIS job or PowerShell script to query each of the servers individually and store the results in a central location, so you can query the data at your leisure.
The query itself is relatively straightforward. Unfortunately, there's something weird with the proxy server here, so you might be best off either waiting for someone else to tell you what it is, or check my blog[/url] where I've been playing with various ways of representing backup information recently.
Thomas Rushton
blog: https://thelonedba.wordpress.com
July 21, 2016 at 4:29 am
Hi,Create a group of servers.mention your job name in script and run following script on groups of registered servers------------------------------------------------------------------------
USE msdb
Go
SELECT dbo.sysjobs.Name AS 'Job Name',
'Job Enabled' = CASE dbo.sysjobs.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Frequency' = CASE dbo.sysschedules.freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQLServer Agent starts'
END,
'Start Date' = CASE next_run_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),next_run_date),1,4) + '/' +
substring(convert(varchar(15),next_run_date),5,2) + '/' +
substring(convert(varchar(15),next_run_date),7,2)
END,
'Start Time' = CASE len(next_run_time)
WHEN 1 THEN cast('00:00:0' + right(next_run_time,2) as char(8))
WHEN 2 THEN cast('00:00:' + right(next_run_time,2) as char(8))
WHEN 3 THEN cast('00:0' + Left(right(next_run_time,3),1) +':' + right(next_run_time,2) as char (8))
WHEN 4 THEN cast('00:' + Left(right(next_run_time,4),2) +':' + right(next_run_time,2) as char (8))
WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1) +':' + Left(right(next_run_time,4),2) +':' + right(next_run_time,2) as char (8))
WHEN 6 THEN cast(Left(right(next_run_time,6),2) +':' + Left(right(next_run_time,4),2) +':' + right(next_run_time,2) as char (8))
END,
------ next_run_time as 'Start Time',
CASE len(run_duration)
WHEN 1 THEN cast('00:00:0'+ cast(run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:' + cast(run_duration as char) as char (8))
WHEN 3 THEN cast('00:0' + Left(right(run_duration,3),1) +':' + right(run_duration,2) as char (8))
WHEN 4 THEN cast('00:' + Left(right(run_duration,4),2) +':' + right(run_duration,2) as char (8))
WHEN 5 THEN cast('0'+ Left(right(run_duration,5),1) +':' + Left(right(run_duration,4),2)+':' + right(run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(run_duration,6),2) +':' + Left(right(run_duration,4),2)+':' + right(run_duration,2) as char (8))
END as 'Max Duration',
CASE(dbo.sysschedules.freq_subday_interval)
WHEN 0 THEN 'Once'
ELSE cast('Every '+ right(dbo.sysschedules.freq_subday_interval,2) + ' '
+ CASE(dbo.sysschedules.freq_subday_type)
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Minutes'
WHEN 8 THEN 'Hours'
END as char(16))
END as 'Subday Frequency'
FROM dbo.sysjobs
--INNER JOIN sys.databases s
--ON s.database_id=s.database_id
LEFT OUTER JOIN dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
FROM dbo.sysjobhistory s
GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
--WHERE Next_run_time <> 0 and dbo.sysjobs.Name='Your job name'
--Specify your job name
ORDER BY [Start Date],[Start Time]
July 21, 2016 at 6:48 am
Is there a query or way to register 100 server name with one query and you will see all the list in your left panel of register server or do i have to individually enter one by one to register all servers ?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply