Ed: SQLDork is a Junior DBA on the team here, learning the DBATools in addition to normal DBA stuff™
Today’s dbatools command is Get-DbaErrorLog, which is very similar to a script we frequently run on client servers to check for errors that need fixing.
Get-DbaErrorLog ` -SqlInstance PRECISION-M7520\SQL2016 ` | Select-Object ` SqlInstance, Source, LogDate, Text ` | Out-Gridview
Basic command usage, using Select-Object to filter out the columns i don’t really care about, then piping the results to Out-Gridview.
Get-DbaErrorLog ` -SqlInstance PRECISION-M7520\SQL2016 ` -LogNumber 1, 0 ` | Select-Object ` SqlInstance, Source, LogDate, Text ` | Out-Gridview
Adding the -LogNumber Parameter to check the archived errorlogs, defaults to current log only.
NOTE: If you want the results ordered by date, the values passed into -LogNumber must be in descending order.
Get-DbaErrorLog ` -SqlInstance PRECISION-M7520\SQL2016 ` -Source spid5s ` | Select-Object ` SqlInstance, Source, LogDate, Text ` | Out-Gridview
The -Source parameter lets us filter out some stuff™. I can’t think of a better way to describe that. The documentation uses -Source Logon as an example, but my sample data didn’t have that so i used a spid.
(Ed: The Source can be a spid, “logon”, “Server”, or anything you see in the errorlog Source column in the SSMS Log Viewer)
Get-DbaErrorLog ` -SqlInstance PRECISION-M7520\SQL2016 ` -LogNumber 5, 4, 3, 2, 1, 0 ` -Text "instance" ` | Select-Object ` SqlInstance, Source, LogDate, Text ` | Out-Gridview
Using -Text as a rudimentary where clause, but this one only supports 1 string. We can do better.
Get-DbaErrorLog ` -SqlInstance PRECISION-M7520\SQL2016 ` -LogNumber 5, 4, 3, 2, 1, 0 ` | Select-Object ` SqlInstance, Source, LogDate, Text ` | Where { ` ( ` $_.Text -Like "*CheckDB*" ` -or $_.Text -Like "*Error*" ` -or $_.Text -Like "*UTC*" ` ) ` -and ( ` $_.Text -NotLike "*found 0 errors*" ` -and $_.Text -NotLike "*finished without errors*" ` -and $_.Text -NotLike "*informational message*" ` ) ` } ` | Out-GridView
This formatting feels very strange to me, but it does a very similar thing to that errorlog query we use that i mentioned earlier.
(Ed: The Where-Object or Where is PowerShell, not DBATools, click here)
Shoutout to Kevin (b|t) for helping me figure out the syntax on this, and also for pointing out that he changed the -SqlInstance parameter to his own machine name when he was testing this. Saved me a bunch of time troubleshooting connection errors.
Speaking of connection errors, follow me (b|t) on twitter, even though i don’t really tweet that much. Unless i retweet pictures of dogs, that’s always a possibility.