Share via

Get-SqlErrorLog

  • Reference
Module:
SQLServer

Gets the SQL Server error logs.

Syntax

Get-SqlErrorLog
   [-Timespan <TimeSpan>]
   [-Before <DateTime>]
   [-After <DateTime>]
   [-Since <SinceType>]
   [-Ascending]
   [[-Path] <String[]>]
   [-AccessToken <PSObject>]
   [-TrustServerCertificate]
   [-HostNameInCertificate <String>]
   [-Encrypt <String>]
   [-ProgressAction <ActionPreference>]
   [<CommonParameters>]
Get-SqlErrorLog
   [-Timespan <TimeSpan>]
   [-Before <DateTime>]
   [-After <DateTime>]
   [-Since <SinceType>]
   [-Ascending]
   [[-ServerInstance] <String[]>]
   [-Credential <PSCredential>]
   [-ConnectionTimeout <Int32>]
   [-AccessToken <PSObject>]
   [-TrustServerCertificate]
   [-HostNameInCertificate <String>]
   [-Encrypt <String>]
   [-ProgressAction <ActionPreference>]
   [<CommonParameters>]
Get-SqlErrorLog
   [-Timespan <TimeSpan>]
   [-Before <DateTime>]
   [-After <DateTime>]
   [-Since <SinceType>]
   [-Ascending]
   [-InputObject] <Server[]>
   [-AccessToken <PSObject>]
   [-TrustServerCertificate]
   [-HostNameInCertificate <String>]
   [-Encrypt <String>]
   [-ProgressAction <ActionPreference>]
   [<CommonParameters>]

Description

The Get-SqlErrorLog cmdlet gets the SQL Server errors logs.

This cmdlet supports the following modes of operation to get an error log:

  • Pass the instance of the SQL Server.
  • Specify the Path parameter of the SQL Server instance.
  • Invoke the cmdlet in a valid context.

Examples

Example 1: Get all logs generated in a specific time frame that have a search word

PS C:\> CD SQLSERVER:\SQL\MyServer\MyInstance
PS SQLSERVER:\SQL\MyServer\MyInstance> Get-SqlErrorLog -Since Yesterday  | Where-Object { $_.Text -match 'Error' } | Format-Table

Date                 Source Text                                  ArchiveNo ServerInstance
----                 ------ ----                                  --------- --------------
6/16/2016 6:04:20 PM Logon  Error: 17828, Severity: 20, State: 4.         0 MyServer\MyInstance

The first command changes the directory to the SQL Server instance.

Example 2: Get all logs generated in a specific time frame

PS C:\> CD SQLSERVER:\SQL\MyServer\MyInstance
PS SQLSERVER:\SQL\MyServer\MyInstance> Get-SqlErrorLog -Timespan '05:30:00' | Format-Table

Date                  Source  Text
----                  ------  ----
6/17/2016 12:00:00 AM spid26s This instance of SQL Server has been using a process ID of 21520 since 6/10/2016 3:56:... 
6/16/2016 6:04:20 PM  Logon   The prelogin packet used to open the connection is structurally invalid; the connectio... 
6/16/2016 6:04:20 PM  Logon   Error: 17828, Severity: 20, State: 4.

The first command changes the directory to the SQL Server instance.

Example 3: Get all logs generated in a specific time frame sorted ascending and grouped

PS C:\> CD SQLSERVER:\SQL\MyServer
PS SQLSERVER:\SQL\MyServer> ls | Get-SqlErrorLog -After '2016-05-10' -Before '2016-06-18' -Ascending | ? { $_.Text -match 'Login failed' } | Group-Object -Property ServerInstance

Count Name                      Group
----- ----                      -----
    1 MyServer                  {{ Date = 6/17/2016 2:00:04 AM, Source = Logon, Text = Login failed for user ... 
    2 MyServer\INST1            {{ Date = 6/10/2016 3:58:46 PM, Source = Logon, Text = Login failed for user

The first command changes the directory to the SQL Server instance.

Parameters

-AccessToken

The access token used to authenticate to SQL Server, as an alternative to user/password or Windows Authentication.

This can be used, for example, to connect to SQL Azure DB and SQL Azure Managed Instance using a Service Principal or a Managed Identity.

The parameter to use can be either a string representing the token or a PSAccessToken object as returned by running Get-AzAccessToken -ResourceUrl https://database.windows.net.

This parameter is new in v22 of the module.

Type:PSObject
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-After

Specifies that this cmdlet only gets error logs generated after the given time.

If you specify the Before parameter, then this cmdlet gets all the error logs generated before the specified.

Do not specify this parameter if you intend to use the Since or Timespan parameters.

The format is defined according to the rules of .Net System.DataTime.Parse().

Type:DateTime
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-Ascending

Indicates that the cmdlet sorts the collection of error logs by the log date in ascending order. If you do not specify this parameter, the cmdlet sorts the error logs in descending order.

When this cmdlet gets error logs multiple sources, the sorting is applied to all the error logs from the same source. The logs this cmdlet get are grouped by source first and then sorted by log date.

Type:SwitchParameter
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-Before

Specifies that this cmdlet only gets error logs generated before the given time.

If the After parameter is specified, the cmdlet defaults to now, meaning that the cmdlet gets all the error logs generated after what you specified for this parameter until the present time.

Do not specify a value for this parameter if you intend to use the Since or Timespan parameters. The format is defined according to the rules of .Net System.Datatime.Parse().

Type:DateTime
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-ConnectionTimeout

Specifies the number of seconds to wait for a server connection before a time-out failure. The time-out value must be an integer between 0 and 65534. If 0 is specified, connection attempts do not time out.

Type:Int32
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-Credential

Specifies a PSCredential object used to specify the credentials for a SQL Server login that has permission to perform this operation.

Type:PSCredential
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-Encrypt

The encryption type to use when connecting to SQL Server.

This value maps to the Encrypt property SqlConnectionEncryptOption on the SqlConnection object of the Microsoft.Data.SqlClient driver.

In v22 of the module, the default is Optional (for compatibility with v21). In v23+ of the module, the default value will be 'Mandatory', which may create a breaking change for existing scripts.

This parameter is new in v22 of the module.

Type:String
Accepted values:Mandatory, Optional, Strict
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-HostNameInCertificate

The host name to be used in validating the SQL Server TLS/SSL certificate. You must pass this parameter if your SQL Server instance is enabled for Force Encryption and you want to connect to an instance using hostname/shortname. If this parameter is omitted then passing the Fully Qualified Domain Name (FQDN) to -ServerInstance is necessary to connect to a SQL Server instance enabled for Force Encryption.

This parameter is new in v22 of the module.

Type:String
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-InputObject

Specifies the server object, as an array, of the target instance that this cmdlet get the logs from.

Type:Server[]
Position:1
Default value:None
Required:True
Accept pipeline input:True
Accept wildcard characters:False

-Path

Specifies the path, as an array, to the instance of SQL Server on which this cmdlet runs the operation. If you do not specify a value for this parameter, the cmdlet uses the current working location.

Type:String[]
Position:1
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-ProgressAction

Determines how PowerShell responds to progress updates generated by a script, cmdlet, or provider, such as the progress bars generated by the Write-Progress cmdlet. The Write-Progress cmdlet creates progress bars that show a command's status.

Type:ActionPreference
Aliases:proga
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-ServerInstance

Specifies the name of an instance of SQL Server, as an array. For default instances, only specify the computer name: MyComputer. For named instances, use the format ComputerName\InstanceName.

Type:String[]
Position:1
Default value:None
Required:False
Accept pipeline input:True
Accept wildcard characters:False

-Since

Specifies an abbreviation for the Timespan parameter.

Do not specify this parameter if you intend to use the After or Before parameter.

The acceptable values for this parameter are:

  • Midnight (gets all the logs generated after midnight)
  • Yesterday (gets all the logs generated in the last 24 hours).
  • LastWeek (gets all the logs generated in the last week)
  • LastMonth (gets all the logs generated in the last month)
Type:SinceType
Accepted values:Midnight, Yesterday, LastWeek, LastMonth
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-Timespan

Specifies a TimeSpan object that this cmdlet filters out of the error logs that do are outside of the time span.

The format of this parameter is d.HH:mm:ss.

This parameter is ignored if you use the Since, After, or Before parameters.

Type:TimeSpan
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-TrustServerCertificate

Indicates whether the channel will be encrypted while bypassing walking the certificate chain to validate trust.

In v22 of the module, the default is $true (for compatibility with v21). In v23+ of the module, the default value will be '$false', which may create a breaking change for existing scripts.

This parameter is new in v22 of the module.

Type:SwitchParameter
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

Inputs

System.String[]

Microsoft.SqlServer.Management.Smo.Server[]