Infrastructure at your Service

Stéphane Haby

SQL Server 2016: New SQL PowerShell CMDLETs for ErrorLog

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 ;-)

Leave a Reply

Stéphane Haby
Stéphane Haby

Delivery Manager