Multi Server Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Multi Server Query

Author

Topic

Peter99
Constraint Violating Yak Guru

498 Posts

Posted - 2012-08-29 : 17:48:28

Hi,

I am looking for multi server query. My objective is to create a report for all sql servers/instances services status, jobs status.
if any one have script, please help.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-08-29 : 18:23:48

We run a report using the CMS as our data source. So we either use CMS in SSMS or we create a report in Reporting Services and point it at the CMS repository.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-08-29 : 18:25:09

Here's a sample script for failed backup jobs:



SELECT     j.name,
         --js.server,
           js.run_date,
           js.run_time,
         --js.run_duration,
           CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(js.run_duration * 9 + js.run_duration % 10000 * 6 + js.run_duration % 100 * 10) / 216e4,108) AS run_duration
          ,SERVERPROPERTY('productversion') 
FROM       msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory js ON j.job_id = js.job_id
WHERE      NAME LIKE '%Backup%'
AND        js.step_id = 0
AND        js.run_status = 0 -- (0 = Failed, 1 = Sucess)
AND        j.enabled = 1
AND        run_date > '20120305'
ORDER BY   js.server, j.name

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-08-30 : 03:43:37

In these situations , we run a Powershell script , which executes a t-sql script for failed jobs (and other various checks). This iterates through the SQL Server Instances using a dedicated security logon. The results are collated in a table and reports are created

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Peter99
Constraint Violating Yak Guru

498 Posts

Posted - 2012-08-30 : 10:29:08

Thanks Tara and Jack for reply.

Jack:
Can I get that powershell script that run tsql script? If available, I will modify as per need.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources