PowerShell changes and new cmdlets for SQL Server 2016

By:   |   Comments (1)   |   Related: > PowerShell


Problem

PowerShell is an important Windows shell which can communicate with SQL Server to help automate and perform several DBA tasks. Windows PowerShell supports more complex logic than T-SQL scripts, giving SQL Server administrators the ability to build robust administration scripts. In SQL Server 2016 there are some significant changes along with new cmdlets and in this tip we will explore a few of these new PowerShell cmdlets.

Solution

As you may know, with SQL Server 2016, SQL Server Management Studio is no longer a part of the standard SQL Server installation and it needs to be separately downloaded. In the first monthly update of SQL Server Management Studio (SSMS) (Version number 13.0.15500.91) since the SQL Server 2016 release, Microsoft has made several changes to PowerShell for SQL Server.

List PowerShell Modules for SQL Server

Before we move further, let's see the modules available with previous versions of SQL Server 2016 using the below query.

Get-Module -ListAvailable -Name SQL*


modules available with previous versions of SQL Server 2016

After I downloaded SQL Server 2016 Management Studio version 13.0.155900.91, I ran the command again.

After downloading SQL Server 2016 Management Studio here are the updated modules

As we can see above, there is a new PowerShell module "SQLSERVER" in SQL Server 2016 as compared to previous versions of SQL Server.

SQL Server PowerShell components are included with the SSMS installer as well as SQL Server engine installation. SQL Server Management Studio will be regularly updated by Microsoft. so in order to provide regular updates Microsoft has changed the SQL PowerShell module from SQLPLS to SQLSERVER. The SQL PowerShell module that ships with SSMS has changed from SQLPS to SqlServer (there is no change to the module used by SQL Server Agent). SSMS has been updated to integrate with SQLTOOLSPS.exe rather than SQLPS.exe. When we launch PowerShell from SSMS it will automatically launch a session with the new PowerShell module.

This is an important step towards separating the components from the existing server component set up and can be updated separately without the need for updating the whole server instance.

Now along with this change, there are several new cmdlets as well. To see the cmdlets in the SQLSERVER module run the following command.

get-command -module SQLSERVER

It will list all cmdlets for the module SQLSERVER.

cmdlets in the SQLSERVER module for PowerShell


cmdlets in the SQLSERVER module for PowerShell


cmdlets in the SQLSERVER module for PowerShell

In this tip we will focus on these PowerShell cmdlets for SQL Server:

  • Get-SqlErrorLog
  • Set-SqlErrorLog

PowerShell cmdlet Get-SqlErrorLog

This is used to get the details from the SQL Server logs. We can filter the error logs with text, dates, etc.

We can get the property and definition of the Get-sqlErrorLog as follows:

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

PowerShell cmdlet Get-SqlErrorLog

Now let's explore this cmdlet with different examples.

1. Get total number of events in the error logs

Get-SqlErrorLog -ServerInstance localhost\sql2016 | measure

We will get the total number of events in the error logs from all error logs files.

Get total number of events in the error logs

2. Filter error logs

Here is an example where we look for the word "login".

Get-SqlErrorLog | Where-Object { $_.text -like "*Login*"} | Out-Grid View

Out-Grid View parameter opens a new grid view window to easily view the logs. If we want to see the events in the cmd prompt only remove this keyword.

Filter error logs

Similarly if we want to filter out with keyword "SQL Server", run the below command.

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

filter out the SQL Server Error Log with a keyword

To filter out the logs between two dates we can use the before and after parameter.

Get-SqlErrorLog -After "2016/09/25"  -Before "2016/10/01" | Where-Object { $_.text -like "*SQL Server*"} | Out-GridView


To filter out the logs between two dates we can use the before and after parameter

To see error logs after a particular date we can run the below command.

Get-sqlerrorlog | Where-Object {$_.Text -like ‘*SQL Server*’ -and $_.Date -gt ‘10/01/2016’}| Out-GridView

see error logs after a particular date we can run the below command

PowerShell cmdlet Set-SqlErrorLog

We can configure the number of error logs using this command. The oldest files are recycled whenever a new log file is created. In SSMS, we can change the default number by right clicking on SQL Server Logs and select Configure.

PowerShell cmdlet Set-SqlErrorLog

We can enable and select the number of SQL Server error log files between 6 to 99.

enable and select the number of SQL Server error log files between 6 to 99

We can get the property and definition of the Get-sqlErrorLog as follows:

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

get the property and definition of the Get-sqlErrorLog

To limit this using PowerShell, return to the PowerShell command line and write the following:

Set-SqlErrorLog -MaxLogCount 10

This will set maximum number of error log files to 10.

set maximum number of error log files to 10

We can now go to the SQL Server error log configuration properties and verify this.

SQL Server error log configuration properties and verify the number of logs

If we want to generate a script to set the maximum number of error log files to 12, run the below command.

 Set-SqlErrorLog -MaxLogCount 12 -Script
 

This will create the T-SQL code to make the change as shown below.

T-SQL code to make the change to the number of SQL Server Error Logs

Summary

It is very helpful to have these 2 new cmdlets in PowerShell. Explore these PowerShell cmdlets and make use of them. In the next tip, I will explore more cmdlets.

Note: If you have a PowerShell script doing Import-Module SQLPS, it will need to be changed to Import-Module SqlServer in order to take advantage of the new provider functionality and new cmdlets.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Rajendra Gupta Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Wednesday, March 1, 2017 - 11:13:50 AM - Hans Back To Top (46846)

 It's a good tip to get informations directly from the different SQL Error Logs.

I fight at the moment wit another problem, propably you'll hava a tip for me:

I will with get-childitem to get informations about the storedprocedures of a DB ( Get-ChildItem "SQLSERVER:\SQL\localhost\DEFAULT\Databases\msdb\StoredProcedures" ).

The Error of this command is: Cannot find path  ..... because it does not exist.

On SQL Server 2012 and 2014 this commands works fine.

Do you have an idea?

 

Tks and best regards

Hans

 















get free sql tips
agree to terms