Databases Reference
In-Depth Information
Interrogating for Warnings and Errors
When a server is not behaving as expected, an error has often occurred. Inspecting the Windows
Event Log is a good place to start an investigation into what may have gone wrong. PowerShell has
full support for the Windows Event Log. The
Get-EventLog
cmdlet lists all the items within an
Event Log. The following example shows the list of items within the application Event Log i ltered
to those whose source is “MSSQLSERVER” and are an error:
Get-Eventlog application '
| Where-Object {$_.Source -eq "MSSQLSERVER" -and $_.EntryType -eq "Error"}
SQL Server also has an Error Log that often contains more detailed information pertaining to SQL
Server-specii c issues. The following example shows how to query the SQL Server Error Log i lter-
ing the results to just the errors (code i le:
PS_ReadSQLErrorLog01.PS1
):
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
$server = New-Object "Microsoft.SqlServer.Management.Smo.Server" "(local)"
$server.ReadErrorLog() | Where-Object {$_.Text -like "Error:*"}
My machine has a single default SQL Server instance. If you are using a named instance, replace
“(local)” with the name of your SQL Server instance.
Using the SQL Server Management Objects it is also possible to quickly i lter the available jobs,
identifying those that failed the last time they ran and are currently enabled. This is a useful script
to run on a regular basis because SQL jobs often contain important management and administra-
tion tasks that are automated, and quickly identifying a failed job will save signii cant time diagnos-
ing issues later. The following script example shows how to query the SMO objects for SQL Server
jobs whose last run outcome failed and are still enabled (code i le:
PS_ReadFailedJobs01.PS1
):
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
$server = New-Object "Microsoft.SqlServer.Management.Smo.Server" "(local)"
$server.jobserver.jobs | where-object {$_.lastrunoutcome -eq "Failed" -and
$_.isenabled -eq $TRUE}
Interrogating Server Performance
PowerShell 2.0 introduced integration with Windows performance counters. This means that you
can quickly view all the performance counters on your machine:
Get-Counter -listSet * | Select-Object -ExpandProperty Paths
You could then i nd all of the SQL Server-specii c counters:
Get-Counter -listSet * | Select-Object -ExpandProperty Paths | where-object {$_ -
like "*SQL*"}
It is also possible to get a sample of a performance counter:
Get-Counter '\Processor(*)\% Processor Time'
Search WWH ::
Custom Search