With the latest release of SQL Server Management Studio(SSMS) 2016 (13.0.15500.91), downloadable here, was introduced new CMDLETs for Always Encrypted, SQL Agent and the  ErrorLog.
SSMS_update01

In this article, I will present you the 2 new CMDLETs for the Error Logs:

  • Get-SqlErrorLog: Retrieves the SQL Server Logs.
  • Set-SqlErrorLog: Sets or resets the maximum number of error log files before recycling.

My first step is to search all commands with “Sql”:

Get-Command | Select Name |Where-Object {$_.Name -like "*Sql"*}

PowerShell_ErrorLog_01

As you can see, I have a lot of commands. I filter with SqlErrorLog and have the detail of both commands:

Get-Command | Where-Object {$_.Name -like "*SqlErrorLog*"} | Format-List *

PowerShell_ErrorLog_02

To have the detail per command, I use these commands:

Get-Command | Where-Object {$_.Name -eq "Get-SqlErrorLog"} | Format-List *
Get-Command | Where-Object {$_.Name -eq "Set-SqlErrorLog"} | Format-List *

 

CMDLET Get-SqlErrorLog

For Example, a simple query to retrieve all backup lines:

Get-SqlErrorLog | Where-Object { $_.text -like "*BACKUP*"} | Out-GridView

PowerShell_ErrorLog_03 You can do the same for the failed login:

Get-SqlErrorLog | Where-Object { $_.text -like "*Failed*"} | Out-GridView

PowerShell_ErrorLog_04

Or directly find all errors between 2 dates with –Before and –After parameters:

Get-SqlErrorLog -Before "2016/06/30" -After "2016/06/28" | Where-Object { $_.text -like "*Error:*"} | Out-GridView

PowerShell_ErrorLog_05 >

CMDLET Set-SqlErrorLog

It is very easy to configure the number of errorlog files with this command:

Set-SqlErrorLog -MaxLogCount [6-99]

PowerShell_ErrorLog_06

After the command:
PowerShell_ErrorLog_07

For the fun, I try to enter a value equal to 1 and a value equal to 100 to see if an error message appears:
PowerShell_ErrorLog_08

It is very nice to have these 2 news CMDLETs in SQL PowerShell 😉


Thumbnail [60x60]
by
Stéphane Haby