January 30, 2012 at 6:59 am
Hi,
i need a script that can connect to multiple sql server instances and collect Mantanance plans status. I want to be able to generat a report daily for successful and failed jobs via reporting services subscription.
I do not want to use a linked server scenario. Please help guys, checking for SQL jobs one by one is very tidius.
January 30, 2012 at 8:09 am
THE-FHA (1/30/2012)
i need a script that can connect to multiple sql server instances and collect Mantanance plans status. I want to be able to generat a report daily for successful and failed jobs via reporting services subscription.I do not want to use a linked server scenario. Please help guys, checking for SQL jobs one by one is very tidius.
Ruling out Linked Servers just makes it more difficult - how about Remote Servers?
An alternative would be to get a third party monitoring tool - even Oracle's Grid Control can do it meaning, monitoring SQL Server.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 30, 2012 at 8:24 am
Create an RDL file (Reporting Services Report), set up multiple datasources (to the servers you want this to run on), then you can use the following query to get basic job information:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SET ARITHIGNORE ON
IF OBJECT_ID ('tempdb.dbo.#ssaj_sssr_agent_jobs_temp') IS NOT NULL
BEGIN
DROP TABLE dbo.#ssaj_sssr_agent_jobs_temp
END
DECLARE @vDate_24_Hours_Agodatetime
DECLARE @vDate_Nowdatetime
SET @vDate_24_Hours_Ago = GETDATE() -1
SET @vDate_Now = @vDate_24_Hours_Ago + 1
SELECT
SJ.name AS job_name
,CONVERT (varchar(19), CONVERT (DATETIME, CONVERT (varchar(8), SJH.run_date) +
' ' + LEFT (RIGHT ('000000' + CONVERT (varchar(6), SJH.run_time), 6), 2) +
':' + SUBSTRING (RIGHT ('000000' + CONVERT (varchar(6), SJH.run_time), 6), 3, 2) +
':' + RIGHT (RIGHT ('000000' + CONVERT (varchar(6), SJH.run_time), 6), 2)), 120) AS last_run_date_time
,(CASE
WHEN SJH.run_status = 0 THEN 'Failed'
WHEN SJH.run_status = 1 THEN 'Succeeded'
WHEN SJH.run_status = 2 THEN 'Retry'
WHEN SJH.run_status = 3 THEN 'Canceled'
WHEN SJH.run_status = 4 THEN 'In Progress'
END) AS last_status
,(CASE
WHEN RIGHT ('000000' + CONVERT (varchar(6), SJH.run_duration), 6) = '000000' THEN '__:__:__'
WHEN LEFT (RIGHT ('000000' + CONVERT (varchar(6), SJH.run_duration), 6), 4) = '0000' THEN '__:__:' +
RIGHT (RIGHT ('000000' + CONVERT (varchar(6), SJH.run_duration), 6), 2)
WHEN LEFT (RIGHT ('000000' + CONVERT (varchar(6), SJH.run_duration), 6), 2) = '00' THEN '__:' +
SUBSTRING (RIGHT ('000000' + CONVERT (varchar(6), SJH.run_duration), 6), 3, 2) +
':'+RIGHT (RIGHT ('000000' + CONVERT (varchar(6), SJH.run_duration), 6), 2)
ELSE LEFT (RIGHT ('000000' + CONVERT (varchar(6), SJH.run_duration), 6), 2) +
':' + SUBSTRING (RIGHT ('000000' + CONVERT (varchar(6), SJH.run_duration), 6), 3, 2) +
':' + RIGHT (RIGHT ('000000' + CONVERT (varchar(6), SJH.run_duration), 6), 2)
END) AS duration
,ISNULL (CONVERT (varchar(19), B.next_run_date_time, 120),'___________________') AS next_run_date_time
,ISNULL (REVERSE (SUBSTRING (REVERSE (CONVERT (varchar(15), CONVERT (money, DATEDIFF (DAY, GETDATE (), B.next_run_date_time)), 1)), 4, 15)),'N/A') AS days_away
INTO dbo.#ssaj_sssr_agent_jobs_temp
FROM msdb.dbo.sysjobs SJ
INNER JOIN msdb.dbo.sysjobhistory SJH ON SJH.job_id = SJ.job_id
AND CONVERT (DATETIME, CONVERT (varchar(8), SJH.run_date)+' '+LEFT (RIGHT ('000000'+CONVERT (varchar(6), SJH.run_time),6),2)+':'+SUBSTRING (RIGHT ('000000'+CONVERT (varchar(6), SJH.run_time),6),3,2)+':'+RIGHT (RIGHT ('000000'+CONVERT (varchar(6), SJH.run_time),6),2)) >= @vDate_24_Hours_Ago
INNER JOIN
(
SELECT MAX (X.instance_id) AS instance_id_max
FROM msdb.dbo.sysjobhistory X
GROUP BY X.job_id
) A
ON A.instance_id_max = SJH.instance_id
LEFT JOIN
(
SELECT SJS.job_id
,MIN (CONVERT (DATETIME, CONVERT (varchar(8), SJS.next_run_date) +
' ' + LEFT (RIGHT ('000000' + CONVERT (varchar(6), SJS.next_run_time), 6), 2) +
':' + SUBSTRING (RIGHT ('000000' + CONVERT (varchar(6), SJS.next_run_time), 6), 3, 2) +
':' + RIGHT (RIGHT ('000000' + CONVERT (varchar(6), SJS.next_run_time), 6), 2))) AS next_run_date_time
FROM msdb.dbo.sysjobschedules SJS
WHERE SJS.next_run_date > 0
GROUP BY SJS.job_id
) B
ON B.job_id = SJ.job_id
SELECT
X.job_name,
X.last_run_date_time,
X.last_status,
X.duration,
X.next_run_date_time,
X.days_away
FROM dbo.#ssaj_sssr_agent_jobs_temp X
ORDER BY X.job_name
IF OBJECT_ID ('tempdb.dbo.#ssaj_sssr_agent_jobs_temp') IS NOT NULL
BEGIN
DROP TABLE dbo.#ssaj_sssr_agent_jobs_temp
END
I use this code and have it emailed to me each morning from each server :: however :: I have much more information included in the email: Server Instance Information, Unused Indexes, Fixed Drives Free Space, Database Size, etc. So I don't need to waste too much time checking things like this. If you'd like I can send you the entire thing...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 30, 2012 at 8:59 am
What I do is more work to setup, but it works great, and handles failures of servers better, IMHO.
I have a db on each instance which is standard. It collects information from that instance, like current server config, backups, jobs, etc. Store it all in tables, in a standard format. So for jobs, you might have instancename, jobID, jobname, date run, status.
Then I use a pull procedure that connects to each instance and pulls that data to a central reporting server, and I can run reports there. If I have some connection issue, or the central server is down, I can go check the individual instance for the results.
You could let your report connect to each server as well, but I'd rather run this from the central server. If there was an issue, you could easily point the report to an individual server as well.
January 30, 2012 at 10:28 pm
hi,
Thanks for the help, but now how do you create multiple intance connection?
January 30, 2012 at 10:29 pm
hi,
Thanks for the help, but now how do you create multiple intance connection or multiple datasources. Anyone with a script to do that?
January 30, 2012 at 11:42 pm
Third party tools are not an option because of Company policies.
January 31, 2012 at 9:39 am
you can use SQLCMD to connect to another server and export data, OPENDATASOURCE, or use SSIS to pull data over.
January 31, 2012 at 2:23 pm
I'll send you something tomorrow that might be useful...currently OOO
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 31, 2012 at 10:12 pm
@ MyDoggieJessie thanks, i will be waiting
February 1, 2012 at 2:45 am
This may be a bit ott, but check this out:
This is the sql server central management system.
You can use policies (using the policy management framework) to check the status of jobs and have ssrs report on it assessed through a browser (as a high level summary - its far more in-depth than that).
February 2, 2012 at 8:37 am
Check your inbox, I've sent you the code needed and the basic instructions on how to set it up (shouldn't take more than a few minutes) - Good luck!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply