0

The sql server error logs show multiple entries with the following keywords:

IO taking longer longer 15 seconds

I want to setup email alerts for these errors. I have email alerts configured for severity level 20. To be able to receive the above IO errors I want to know what is their severity level?

If they are not belonging to any severity level, then how to setup email alerts for such messages?

4

The error number is 833, so it is just a matter of setting up an Agent Event Alert based on that error number. The number isn't written to eventlog by default, however, so you need to configure that first using sp_altermessage:

EXECUTE sp_altermessage 833, 'WITH_LOG', 'true';  
1

Long IO requests are severity 10 error - you can view the information here:

select * from sys.messages
where   language_id = 1033
        and message_id = 833

First you would have to make those errors logged to the application log, for this execute the script Tibor posted in his answer. This will change the is_event_logged from 0 to 1

Then you need to create an alert for this error number:

USE [msdb]
GO

EXEC msdb.dbo.sp_add_alert @name=N'Long IO Requests', 
    @message_id=833, 
    @severity=0, 
    @enabled=1, 
    @delay_between_responses=60, 
    @include_event_description_in=1, 
    @category_name=N'[Uncategorized]', 
    @job_id=N'00000000-0000-0000-0000-000000000000'

And don't forget to add operator (email address where to alert emails would be sent) in the created alert's properties

0

There are quite some ways you can do this. Getting data extracted from sys.messages about events and wrap in SQL job to look for the text conditions you may want to search for.

Please refer to https://blog.dbi-services.com/sql-server-get-email-notifications-for-error-log-entries/ and https://www.mssqltips.com/sqlservertip/2307/automate-monitoring-sql-server-error-logs-with-email-alerts/

to get an idea on how to set this up

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.