Subscribe to this blog
Briefcase
Print
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:
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.
Posted by Steve Jones on 19 October 2010
Nice trick. I can see this as being very handy, storing the results in table and reporting exceptions.
Posted by Grant Fritchey on 19 October 2010
Thanks. It wouldn't be hard at all to simply pipe the output to an insert statement to get it into a table.
Posted by John Mitchell on 20 October 2010
Grant, I know you said this is a Powershell story, but here's a T-SQL method:
EXEC master.dbo.sp_servicecontrol 'QUERYSTATE', 'SQLSERVERAGENT'
Not sure whether this is an undocumented stored procedure, but it works for us.
John
Posted by azhar.khan 32723 on 20 October 2010
Small type John, your statement should have used xp_servicecontrol
ie
EXEC master.dbo.sp_servicecontrol 'QUERYSTATE', 'SQLSERVERAGENT'
Posted by Allan Green on 20 October 2010
You can also get the same info (plus a little bit more!) by using Powershell's get-wmiobject cmdlet
get-wmiobject win32_service -computername (get-content "sqlservers.txt") |
where {$_.name -like "SQL*AGENT" }
Posted by Mike Hinds on 20 October 2010
I, and I'm sure many like myself, was drawn to the article because of the title. I would thoroughly love to know what I'm doing with Powershell and SQL. I need just two things:
1) Where to start with PowerShell (as a total n00b)
2) When, where, and how to add SQL Server to the mix
Any recommended web pages and/or books?
Posted by Patrick Flynn on 20 October 2010
For some very good into to Powershell goto the Midnight DBA site (run by Sean and Jen McCowen at midnightdba.itbookworm.com/Admin.aspx
This has numerous Videos that will get you started.
Posted by Grant Fritchey on 20 October 2010
For books, the best one I've found is Don Jones book, linked above. For web sites, absolutely Jen & Sean, Allen White, and Aaron Nelson. Those are some of the best resources I know for learning the SQL side of Powershell.
Posted by jasona.work on 20 October 2010
I'm going to agree with the others above. If you're looking to get a handle on Powershell, and how to use it, The Don Jones book (Powershell 2.0 TFM) is the best I've found. It's aimed at general Powershell, and I've yet to get past the first couple chapters, but even there I've found ideas.
Posted by Artem Ervits (@dbist) on 20 October 2010
I learn my Powershell with SQL from this guy www.databasejournal.com/.../Muthusamy-Anantha-Kumar-aka-The-MAK.htm
He has a book on administering SQL with Powershell as well.
Posted by RichardB on 20 October 2010
I must be doing something stupid - but I am getting "A parameter cannot be found that matches parameter name 'computername'"
Any idea?
Rich
Posted by Grant Fritchey on 20 October 2010
Strange... I just copied & pasted & it worked again. Not sure what's up.
Posted by YSLGuru on 20 October 2010
@RichardB
Did you replace -ComputerName with the actual name of your SQL Server instance? I'm new to PS and thats what I did and I got that same error.
Posted by YSLGuru on 20 October 2010
Question for the PowerShell gurus here:
Is it possible to specify a txt file (for the servers.txt file) that is located on a network share and not just in the Default path of the SQL Instance you are running Powershell from? I tried the below but get the message that it can not find the path and I have verified the path is right:
Get-Service -DisplayName "SQL Server Agent*" -ComputerName (Get-Content "\\DOMFS02\MyPath\SERVERS_LIST.txt") | Select-Object MachineName,Status
Posted by Grant Fritchey on 20 October 2010
No, replacing -ComputerName isn't the answer.
For the UNC, try it without the string quotes. They're actually not necessary as part of the path. I'm still learning this stuff too.
Posted by Bob Lee on 20 October 2010
sp_servicecontrol? azhar.khan I'm interested. I think someone must have written that for you guys. Can you publish the sp?
Posted by Bob Lee on 20 October 2010
Wait a minute you mean Xp_servicecontrol right?
Posted by jac on 21 October 2010
I just tried that command and it only returns the first server in the list. Help suggests comma seperated but this doesnt work either.
Anyone get what im doing wrong
PS C:\jac\temp> Get-Service -Name SQLSERVERAGENT -ComputerName SHF01-SSBE03, SHF01-SSBE04 | format-table -property MachineName, Status -auto
Posted by Grant Fritchey on 21 October 2010
Try doing hard returns between the servers instead of commas.
Posted by thildebrand on 22 October 2010
You could do something like this, where "DB_Servers.txt" has a list of your SQL servers.
$servers = Get-Content "c:\DB_Servers.txt"
foreach($server in $servers)
{
Write-Host $server
Get-Service -ComputerName $servers -Name SQLSERVERAGENT | Format-List status
}