Every company I’ve worked at had some type of standard schedule to apply (non critical) Windows patching. Most (if not all) of the time this ends up requiring a reboot of the whole environment which no matter how good your shutdown/startup documentation is tends to be a worrysome time.
If you haven’t played around with SQL Server 2008′s new Central Management Server please take some time to read this great article by Brent Ozar about it:
SQL Server 2008′s new Central Management Server – Brent Ozar
OK so now after reading that article you should be “in the know” about the new CMS. Some of the groupings I have in my CMS are called DEV, UAT, and PROD. So if I highlight the PROD group and select “New Query” the first thing I see at the bottom of my Management Studio Window is:
So there is my first “sniff test” that all my SQL Server Instances in my PROD group are alive and well… but what about the SQL Server Agent service… is it running as well?
Copy the code below into your Management Studio query window (UPDATED to include @@SERVERNAME):
IF EXISTS ( SELECT 1
FROM MASTER.dbo.sysprocesses
WHERE program_name = N'SQLAgent - Generic Refresher')
BEGIN
SELECT @@SERVERNAME AS 'InstanceName', 1 AS 'SQLServerAgentRunning'
END
ELSE
BEGIN
SELECT @@SERVERNAME AS 'InstanceName', 0 AS 'SQLServerAgentRunning'
END
Your result set should have all the instance names in your PROD CMS Group along with a True/False column as to whether or not the SQL Server Agent Service is up and running.
In utilizing a Central Management Server and running this script you now have a good indication as to whether or not you can connect to all your SQL Servers as well as the status of the SQL Server Agent Service on each of those instances. Obviously we’re just scratching the surface of the great things you can do with CMS but if you haven’t played around with this new feature this should give you a good place to build from.
Enjoy!!
Might want to add servername to each result set, so you can easily tell which one is down.
hehehe sorry bout that… by default I have the “Add Server Name to the results” set to true for my CMS queries.
Tools -> Options -> Query Results -> SQL Server -> Multiserver Results
I’ll change the post
thanks for the catch.
[...] Check SQL Server Agent status on all SQL Servers – Another tip here, courtesy of Colin Stasiuk. [...]
[...] I wrote a blog post back in 2009 called: Check SQL Server Agent Status on all SQL Servers [...]