Get-SqlErrorLog
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\MyInstanceThe 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 userThe 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[]