May 16, 2017 at 10:52 am
Hi all,
New in my environment. We use Nagios to log in and monitor our DB servers, but from what I've seen the metrics are quite poor.
Ideally, in addition to system views, I'd like to query the Error log to look out for errors 823/4/5, as well as Severity levels 16 and above.
However I'm not sure whether to use XP_ReadErrorLog, which I understand is unsupported, undocumented, and will be withdrawn possibly without notice. Also, DBmail is not an option, as we work with one, maybe two servers per many clients, and the work involved getting all the
Windows admins co-operating to set up AD accounts would hugely outweigh the benefit.
If anyone has a list of best alerts/counters or any other suggestions, these are warmly welcomed.
Thanks,
JB
May 16, 2017 at 11:20 am
JaybeeSQL - Tuesday, May 16, 2017 10:52 AMHi all,New in my environment. We use Nagios to log in and monitor our DB servers, but from what I've seen the metrics are quite poor.
Ideally, in addition to system views, I'd like to query the Error log to look out for errors 823/4/5, as well as Severity levels 16 and above.
However I'm not sure whether to use XP_ReadErrorLog, which I understand is unsupported, undocumented, and will be withdrawn possibly without notice. Also, DBmail is not an option, as we work with one, maybe two servers per many clients, and the work involved getting all the
Windows admins co-operating to set up AD accounts would hugely outweigh the benefit.If anyone has a list of best alerts/counters or any other suggestions, these are warmly welcomed.
Thanks,
JB
Not sure how you would handle any notifications - pager and net send options are going to be removed in a future version of SQL Server. It already has the standard warnings not to use in new development, modify applications using those, etc. You could execute a job in response to an alert - not sure how you alert in general with your setup- but that could be an option
For error logs, you can also read the SQL Server logs with Powershell if that's an option.
Brent Ozar has a pretty good list of alerts to setup on his site:
Blitz Result: No SQL Server Agent Alerts Configured
Sue
May 16, 2017 at 11:48 pm
This was removed by the editor as SPAM
May 17, 2017 at 2:20 am
Quite a lot of 'Not sures' around today, but appreciate the replies 🙂
Adding another - I'm not sure I made it explicit, probably not, but I'm after where in SQL to point our alerting software to find these alerts - remember, Database Mail is not an option.
May 17, 2017 at 2:33 am
JaybeeSQL - Tuesday, May 16, 2017 10:52 AMHowever I'm not sure whether to use XP_ReadErrorLog, which I understand is unsupported, undocumented, and will be withdrawn possibly without notice. Also, DBmail is not an option, as we work with one, maybe two servers per many clients, and the work involved getting all the
Windows admins co-operating to set up AD accounts would hugely outweigh the benefit.If anyone has a list of best alerts/counters or any other suggestions, these are warmly welcomed.
Thanks,
JB
I wouldn't worry too much about xp_readerrorlog being undocumented. It's been around for a long time, and I'd be surprised if it were withdrawn. If that does worry you, though, do as Sue suggested and use your favourite scripting language to read the errorlog, errorlog.1, errorlog.2 etc files directly.
JaybeeSQL - Wednesday, May 17, 2017 2:20 AMAdding another - I'm not sure I made it explicit, probably not, but I'm after where in SQL to point our alerting software to find these alerts - remember, Database Mail is not an option.
Not sure what you're going to do without Database Mail - maybe pull all the results into one place and write some reports that get regularly checked? If you already have alerting software in place, it should be a fairly simple matter to get it to look at these reports. What is your alerting software, incidentally, and how do its alerts get to you?
John
May 17, 2017 at 3:25 am
John Mitchell-245523 - Wednesday, May 17, 2017 2:33 AMJaybeeSQL - Tuesday, May 16, 2017 10:52 AMHowever I'm not sure whether to use XP_ReadErrorLog, which I understand is unsupported, undocumented, and will be withdrawn possibly without notice. Also, DBmail is not an option, as we work with one, maybe two servers per many clients, and the work involved getting all the
Windows admins co-operating to set up AD accounts would hugely outweigh the benefit.If anyone has a list of best alerts/counters or any other suggestions, these are warmly welcomed.
Thanks,
JB
I wouldn't worry too much about xp_readerrorlog being undocumented. It's been around for a long time, and I'd be surprised if it were withdrawn. If that does worry you, though, do as Sue suggested and use your favourite scripting language to read the errorlog, errorlog.1, errorlog.2 etc files directly.
JaybeeSQL - Wednesday, May 17, 2017 2:20 AMAdding another - I'm not sure I made it explicit, probably not, but I'm after where in SQL to point our alerting software to find these alerts - remember, Database Mail is not an option.Not sure what you're going to do without Database Mail - maybe pull all the results into one place and write some reports that get regularly checked? If you already have alerting software in place, it should be a fairly simple matter to get it to look at these reports. What is your alerting software, incidentally, and how do its alerts get to you?
John
Not sure if you read the OP we use Nagios, which logs in to SQL Server, polls...whatever parts of SQL need to be polled, and sends us alerts via email. As 3rd line support I'm on the email list. I'm looking to rationalise our setup, as we aren't currently alerting on errors 16 and above, nor errors 823/4/5.
May 17, 2017 at 3:30 am
I think I'd just use xp_readerrorlog to pull all error details into a table, then poll that and alert accordingly.
John
May 17, 2017 at 4:14 am
John Mitchell-245523 - Wednesday, May 17, 2017 3:30 AMI think I'd just use xp_readerrorlog to pull all error details into a table, then poll that and alert accordingly.John
As for errors, do we need to actively enable Severity levels, or will these levels just appear in the log? Seems basic, but I can't find this explicitly stated anywhere.
May 17, 2017 at 4:38 am
John Mitchell-245523 - Wednesday, May 17, 2017 4:21 AMI'm not entirely sure, although I can see severity 14 and severity 18 errors in the errorlog of one of our servers.Edit - take a look at the Logging Errors section of this page.
John
Any chance you could connect in and take a look at SQL Server Agent > Alerts, please? If you have none configured then that'd prove it. Otherwise the mystery remains...
May 17, 2017 at 4:58 am
I think it's to do with sys.messages, not SQL Server Agent alerts.
SELECT
m.message_id
, m.severity
, m.text FROM sys.messages m
JOIN sys.syslanguages l ON m.language_id = l.msglangid
WHERE m.is_event_logged = 1
AND l.name = @@language
ORDER BY m.severity
John
May 17, 2017 at 5:29 am
May 17, 2017 at 5:36 am
That's not what I mean. The table contains each possible message, but not each instance of when they were raised. As I understand it, if is_event_logged is 1, an entry will appear in the errorlog every time the error is raised.
John
May 17, 2017 at 8:22 am
It was a good try, but no cigar ;(
I ran :
Select COUNT(*)
FROM sys.messages M
Where M.text like '%Login failed for user%'
This gave me 16 results.
I copied the output of EXEC master.dbo.xp_readerrorlog 0, 1 and pasted it into a s/sheet, filtered A-Z and deleted the rows north and south of 'Login failed for user', this counted 36 rows remaining.
Thanks anyway 😉
May 17, 2017 at 8:42 am
OK, so there are 16 different types of login failure message, and you have 36 instances of login failure messages in your errorlog. That makes sense to me. It sounds like you've got what you need, even if you think you haven't.
By the way, to avoid fiddling about with spreadsheets, do this:EXEC master.dbo.xp_readerrorlog 0, 1, 'Login failed for user'
John
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply