• Skip to main content
  • Skip to primary sidebar

DallasDBAs.com

Explaining SQL Server in plain english

  • Services
    • SQL Server Health Check
  • Pocket DBA®
  • Blog
    • Speaking
  • Testimonials
  • Contact
  • About

TIL: Get-DbaErrorLog

May 29, 2020 by SQLDork Leave a Comment

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.

Pluralsight course for new SQL Server DBAs

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to email a link to a friend (Opens in new window)
  • Click to print (Opens in new window)

Related

Filed Under: TIL, Tools, Troubleshooting Tagged With: DBATools.io, syndicated

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Search

Sign up for blogs, DBA availability and more!


Categories

  • Accidental DBA
  • Apprentice
  • Azure
  • backup
  • backup
  • Beginner
  • Career
  • Configuration
  • Cycling
  • Dallas DBAs
  • Deployment
  • Emergency
  • Encryption
  • EntryLevel
  • Fries
  • Goals
  • HADR
  • HealthCheck
  • Index
  • Install
  • IRL
  • Management
  • Migration
  • MySQL
  • Oracle
  • PASS
  • Performance
  • Personal
  • Pluralsight
  • Podcast
  • PowerShell
  • red gate
  • Restore
  • Security
  • Speaking
  • SQL
  • sql 2005 log shipping suspect
  • sql injection
  • SQLSaturday
  • SSIS
  • SSMS
  • Summit
  • TIL
  • Tools
  • Training
  • Troubleshooting
  • TSQL
  • TSQL2sday
  • Uncategorized
  • Upgrade
  • vendor code modify
  • video
  • vNext

Copyright © 2023 · Genesis Sample on Genesis Framework · WordPress · Log in

Privacy Policy
 

Loading Comments...