SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Powershell is Really Easy… If you know what you’re doing

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.

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).


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.


Posted by azhar.khan 32723 on 20 October 2010

Small type John, your statement should have used xp_servicecontrol


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?


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


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


Leave a Comment

Please register or log in to leave a comment.