Next week Chrissy LeMaire ( b | t ), Laerte Junior ( b | t ), Rob Sewell ( b | t ) and I will be hosting a webinar covering the new PowerShell cmdlets that have just arrived in the end-of-June update of SSMS 2016.
You can register for the webinar here.
Until then, here is a quick summary of what’s available now if you want to get started playing around with things yourself.
| CMDLET | Description |
| Get-SqlAgentJobHistory | Returns the JobHistory present in the target instance of SQL Agent. |
| 5 More SQL Agent cmdlets | SQL JobSchedules and SQL Agent Job Steps. Read more in Chrissy’s post |
| Get-SqlErrorLog | Retrieves the SQL Server Logs. |
| Set-SqlErrorLog | Sets or resets the maximum number of error log files before they are recycled. |
| Plus 17 more Encryption cmdlets | For more on that check out the post over on the Data Platform Insider blog |
Get-SqlErrorLog
I want to tell you a great story about Get-SqlErrorLog. This new cmdlet is possible thanks to some capabilities in the old SQLPSX module of PowerShell, as well as a contribution by Nic Cain ( b | t ). Huge thanks to Nic for posting a version of the Get-SqlErrorLog he was already using, which helped give the SQL Tools team some ideas of what people may want from this cmdlet.
A couple weeks ago I ran into a memory issue with the Clustered Columnstore Indexes in SQL Server 2016. (The problem I’m talking about is already fixed and will be out in CU1 of SQL Server 2016.) To make a long story short the SQL team needed the exact sequence of events I went through to receive this error message. The great thing about SQL Server 2016 is that this was easier than ever to do. Since I already had turned on Query Store, the SQL team would easily be able to see which queries I had run which had generated an error. If you’d like to do that yourself, just run this query.
SELECT qt.query_sql_text, rs.execution_type_desc
FROM sys.query_store_query_text qt
JOIN sys.query_store_query q
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan p
ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs
ON p.plan_id = rs.plan_id
WHERE execution_type !=0
So I’ve got the queries, I’ve got the backups, now I just need to send them the sequence of events. But wait, I can do even better than that! Since I had recently been discussing different options for Get-SqlErrorLog I remembered that I could use that to help the SQL team even more. I fired up PowerShell, pointed Nic’s Get-SqlErrorLog at my instance, and then piped the output to Out-GridView.
Get-SqlErrorLog-sqlserver localhost\sql2016|
Out-GridView
Once the GridView popped up, I typed “memory” into the box at the top, that instantly filtered down the entire results of my SQL Error Log down to only the messages which contained the word “memory” in them. I then clicked in the grid of results, did Ctrl+A then Ctrl+C. Next I pasted all 970 of those error messages into an Excel spreadsheet and included it in what I sent off to the SQL team with my backup file.
I don’t know how much that helped the SQL team track down the problem I was running into, but I do know that it took me less than 30 seconds to gather that information for them.
In the future, hopefully we will have a Write-DataTable cmdlet to allow us to save this kind of data straight into a SQL Server table. Until then, this is still a great step up over what we’ve had.
Not impressed?
Don’t forget that by throwing this in a quick foreach loop around your Registered Servers or Central Management Server, you can grab the error logs from all your SQL Servers, and then filter the results in just seconds.
Join the Conversation
We’re all really excited to finally get our hands on these cmdlets, but we’re also excited that we’ll be getting even more cmdlets when SSMS 2016 updates again. That next round of cmdlets is still being defined right now and we again invite you Up-Vote to join the conversations we’re having with the SQL Tools team on the Trello board we’ve setup. If you want to converse more about these topics, we’ve even setup a Slack Channel to do just that. Everyone in the SQL Community is welcome to join the Slack channel, that’s why we named it SQLCommunity.Slack.com! J
Read More
My fellow PowerShell VC leaders have also posted about these new capabilities and you can read their thoughts here:
- To read the full announcement from Microsoft check out this post on the Data Platform Insider blog.
- To read more about the new SQL Agent cmdlets read this post from Chrissy LeMaire.
- To read more about the new Always Encrypted cmdlets read this post from Rob Sewell
- To read more about the new -OutputAs parameter for Invoke-SqlCmd read this post from Laerte Junior.
