Sigh… I just spent almost a full work day trying to come up with, what turned out to be a single line script. The requirements were simple. After patching of servers is completed, and everything is rebooted, sometimes, not often, but sometimes, SQL Server Agent doesn’t restart correctly. I was asked to put together a script that will check a list of servers to see if Agent is running. Any PowerShell guru’s reading this are either laughing or crying, already.
So, I started exploring how to connect to the server using using some type of PS-Drive, but I quickly figured out that I’d have to loop through the servers that way. Plus, I don’t think there’s a way to check Agent status through the SQL drive. So that went out the window. Then, it was to use Invoke-SQLCmd. After all, I can call multiple servers through the -ServerInstance property, so I just need to find a good T-SQL statement to check Agent status… Googling & Binging didn’t turn up a good T-SQL statement to check Agent status (not saying there isn’t a way, but I couldn’t find one easily, plus, this is a story about PowerShell). On to SMO. All I had to do was figure out how to connect to the server and the SQL Agent object through SMO. I’d have to loop through the servers, but for such a simple script, that shouldn’t be hard. Ever tried to connect directly to the Agent through SMO? Me neither. It’s a pain.
Finally, I referred back to Don Jones (blog|twitter) book, Windows Powershell 2.0, TFM (and a more appropriately named book, there isn’t), and noticed a statement, Get-Service… Hey! Isn’t SQL Agent basically a service? Yes. Yes it is. Here’s the final script, in all it’s simplistic glory:
[sourcecode language=”powershell”]Get-Service -DisplayName "SQL Server Agent*" -ComputerName (Get-Content "servers.txt") | Select-Object MachineName,Status
Yep, it was that easy. Kids, don’t try this at home. Take the time to learn what you’re doing. Don’t assume you know.
Grant, I use that in the performance metrics script to test if the SQL Server instance is running before attempting to gather perf stats from it. You attended that session as I recall. I guess it shows you (generic ‘you’, not specifically you) retain the information you need at the time, and subconsciously ignore the info you aren’t ready for yet.
Way too true. Yeah, I remember your session. Thinking about it, I’ve pretty sure I’ve got notes including a comment on that method. Actually, I’ve seen this used all over the place, but when it came time to write my own code… everything seems to have left my tiny brain. Still, figured it out, even if it took a lot longer than it should have.
Grant, I think that’s a great post, takes time to absorb new tools, and for a while we’re back to beginners, wondering if we’re doing it the “right” way and if we forgot to hit the turbo switch. Keep posting these lessons.
*OR*
you could just look at the dashboard in OpsMgr to see what agents are running or not…
Right. You’re assuming my co-workers know where OM is located…
Thanks Andy. Lots of my blog posts are just me figuring stuff out, publicly.
Grant,
try this:
Get-Service -DisplayName “SQL Server Agent*” | where {$_.status -eq “Stopped”} | selec
t name, status, displayname | write-host -background “RED” -foreground “Black”
Glad i caught this – I was fiddling with different ways to do this for my SQL Sat 58 presentation coming up on monitoring. After having such a problem myself recently – I used WMI, but if you have an active/active cluster that does not work too well.
Thanks for sharing, learned yet another thing about powershell.
You could also automate the service restart as well as logging the servers where the service is stopped (I assume that would be your next step):
Get-Service -DisplayName “SQL Server Agent*” -ComputerName (Get-Content “servers.txt”) | Start-Service -PassThru | Select-Object MachineName,Status
WIDBA, glad it helped.
Alisdair, yeah, possibly. I suspect we’ll want to determine why it didn’t restart before we just automatically restart it.
Artem, nice touch.
Just wanted to throw this back in – I was adding this to my monitoring script this AM and came up with a different shot at it. Since we do the multi instance per server style, I needed a way to query the agent for each sql instance I am checking and I pass in a SMO SQL object. (There are a number of other ways to do this, I tried this one, it may have some “gotchas” for other installs.)
$computerName = $SmoSqlServer.NetName
$sqlName = $SmoSqlServer.Name
$sqlAgentName = $smoSqlServer.Name.Replace($computerName,”SQLAgent”).Replace(“\”,”$”);
Get-Service -Name $sqlAgentName -ComputerName $computerName `
| where {$_.status -eq “Stopped”} `
| Select-Object MachineName,DisplayName,Status
WIDBA,
Only thing wrong with that approach is that you’ll have to loop the calls. I try to avoid that as much as possible, where possible.
[…] I was reading the post Powershell is Really Easy… If you know what you’re doing and it really struck a chord with […]