March 2, 2021 at 12:00 am
Comments posted to this topic are about the item Monitor SQL Server Error Logs with PowerShell
March 2, 2021 at 4:24 pm
This sounds very good. Short and succinct article with all the relevant steps. I will give this a go as soon as I get a bit of time.
March 6, 2021 at 7:32 pm
Nice and useful work. Clear steps, I followed them and everything works like a charm. It would be very interesting to alternate the colors of the rows to be more readble and make rd if severe errors, well done
December 25, 2023 at 11:30 pm
I like what you have going on here. But a bit over the top IMHO.
Why PowerShell? Since anything can execute the xp_readerrorlog from a remote box then why not much more simply have the management server periodically execute the xp from its local SQL Agent?
I'd say a variant of this PowerShell might best be served installed local to each server being monitored and run under that box's Windows Task Schedule. This variable script can push it's results to the management server. Then if a given monitored server is experiencing a significant problem at least the local script might be able to catch something and push to the manager.
The management server should also have a process to look into the log table to validate all remote instances are checking-in as expected. And add a PowerBI dashboard to trigger an email for any concerns - not use the PW script to send any emails.
December 27, 2023 at 6:55 pm
Why PowerShell? Since anything can execute the xp_readerrorlog from a remote box then why not much more simply have the
management serverperiodically execute the xp from its local SQL Agent?
How is a management server going to connect to the different servers? Unless you set up a series of linked servers or use SQL command, you simply cannot "periodically execute the xp". PowerShell would be my choice for this.
I'd say a variant of this PowerShell might best be served installed local to each server being monitored and run under that box's Windows Task Schedule. This variable script can push it's results to the
management server. Then if a given monitored server is experiencing a significant problem at least the local script might be able to catch something and push to the manager.
That may allow for customization per server, but wouldn't a stored proc in your DBA database executed from a SQL job be a bit simpler to maintain? There would be no need to log into a server to set this up.
The
management servershould also have a process to look into the log table to validate all remote instances are checking-in as expected. And add a PowerBI dashboard to trigger an email for any concerns - not use the PW script to send any emails.
Sounds like a duplication of monitoring software? I think I would want to know if a server is not responding on a far more frequent schedule than this.
This is a good article, and it is very useful. Here are some random thoughts...
Your exclude list may get very long. Should this be an Include list? If the list was on each server, you could customize it for each server. There may be servers where you do not care about some messages, and others you may. Like failed logins. I really do not care about failed logins on dev servers, but on prod boxes I do. Something like the "differential backup" I would want to see on the servers where we do not take a diff backup.
The use of PowerShell vs. something else is interesting. I've done this same thing for years with a stored proc and a SQL job. To each his own!
Is there any mechanism to notify different groups per server? DBA's get all servers, BI folks get notified about the BI servers, and so forth? Leveraging the operators on each server may be an improvement.
I attached the proc I have been using forever to do this same thing. It could use some attention, I'm sure.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
January 31, 2024 at 2:20 pm
I appreciate your effort and sharing this with the community. I noticed the following:
* Neither image was clickable to present a larger one that should have increased legibility; the first image was smaller than the second, so I had to rely on the zoom capability of the browser to view it. My backup would have been to use the Magnifier of Windows.
* Immediately after the second image, there was a blank gap longer than both images combined.
* Within "Installation Walkthrough," the name of the SQL script was misspelled; it appeared as "script 7_Populate_SQLErrorlog_exlude_table.sql." Please change "exlude" to "exclude."
* Within "Scheduling," its opening statement was, "The way I run this is to schedule this is with the Windows Task Scheduler." Please delete the second instance of "is."
Thank you.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply