system table for SQL server agent

Question

Post reply

system table for SQL server agent

sql.sanya

SSC-Addicted

Points: 453
More actions
December 28, 2008 at 7:20 am

#133452

Im having 100+ SQL servers in my project. I have check whether all the sql server agent is running or not. SO is there ant system table to check whether sql agent is running or not

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

No, but you can check for connections from it:

select * from sys.sysprocesses

where program_name like '%SqlAgent%'

However I wouldn't trust this kind of check for service status necessarily. I would use VBScript and WMI external to SQL Server. You can check your service status using the code on the following link:

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=ServiceStatusMon&referringTitle=AutomatedDBA

Jeffrey Williams SSC Guru Points: 90305 More actions · Answer 2

I would use Powershell and WMI to check service status.

This will get you started, all you need to do is look at the examples for how to loop through a list of servers to expand upon this:

PS> $computer = '.'

PS> $services = Get-WMIObject Win32_Service -computer $computer | where {$_.Name -like 'SQLAgent*'}

PS> $services | Select Name, State, Status

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

Since Powershell is Microsofts future for scripting, Jeffery's recommendation is actually a better one. It uses the same WMI namespaces that the vbscript in the link I provided would, but it uses the more current Powershell over the older VBScript. I should really take the time to get into powershell some more.

sql.sanya SSC-Addicted Points: 453 More actions · Answer 4

sql.sanya

SSC-Addicted

Points: 453

December 28, 2008 at 9:01 pm

#918239

Thanks alot for ur valuable solutions.

Vee Hall of Fame Points: 3028 More actions · Answer 5

But if you are not familiar with WMI or Powershell.

You can use

EXEC master..xp_servicecontrol 'QueryState', 'SQLServerAgent'

you can set a batch job for all your 100 servers and report the output to a file.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

Vee (12/29/2008)
But if you are not familiar with WMI or Powershell.
You can use
EXEC master..xp_servicecontrol 'QueryState', 'SQLServerAgent'
you can set a batch job for all your 100 servers and report the output to a file.

I would opt to use a supported method over an undocumented Extended procedure. Microsoft does not guarantee the functionality or continued existence of undocumented extended procedures from version to version. Just a word of caution there. While it does work to solve your problem, I wouldn't personally use or recommend using it though.

Great1 Newbie Points: 9 More actions · Answer 7

Scripting will get you started with monitoring the Agents, but once you create it, you'll begin to find that you'll want to monitor each job, then the instance and the data, maintenance jobs, etc.

If you have that many production instances, then I'd suggest finding out if your company has or would consider getting Microsoft System Center Operations Manager (SCOM) 2007.

Of course, it allows you to monitor and manage pretty much every system in the envirionment, but the SQL specific management packs will allow you to monitor all of the important stuff including SQL services and jobs, as well as the health of the system itself.

Hope this helps!

Jeffrey Williams SSC Guru Points: 90305 More actions · Answer 8

To add to what Jonathan has stated - I prefer a scripted solution since that does not require an instance of SQL Server be available for monitoring. For something like this - it can all be done in Powershell (even notification) without using a database at all.

If your company can afford it - purchasing a solution to monitor is ideal. However, I have found that even large companies are reluctant to spend that kind of money - even when it is critical to their business. Most solutions for monitoring are not cheap - and those that are, you definitely get what you pay for.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs