November 17, 2010 at 2:17 pm
I've been seeing some messages being logged noting:
SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file etc ....
and we are currently troubleshooting the issue, but is there a way to setup an alert based on this message to send me an email when/if it occurs? Since it's not being logged as an error I am not sure how or if it's possible.
November 17, 2010 at 9:56 pm
You may create an Alert for Error # 833 and configure it to send an email to the operators. Here is an example
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'833Alert',
@message_id=833,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'833Alert', @operator_name=N'Production Support DBAs', @notification_method = 1
GO
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
November 18, 2010 at 8:27 am
Adiga (11/17/2010)
You may create an Alert for Error # 833 and configure it to send an email to the operators. Here is an example
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'833Alert',
@message_id=833,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'833Alert', @operator_name=N'Production Support DBAs', @notification_method = 1
GO
How did you find out that is an error 833? That's not listed anywhere in the log. I'm obviously missing something.
November 18, 2010 at 8:31 am
The Error number will not be printed in SQL Server Error Logs. Please navigate to Application Event Log, you will find the error number listed there.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
November 18, 2010 at 8:32 am
Adiga (11/18/2010)
The Error number will not be printed in SQL Server Error Logs. Please navigate to Application Event Log, you will find the error number listed there.
Thanks for the direction.
November 19, 2010 at 12:34 pm
It is a good thing to initially log and notify on this type of message. However there may be underlying storage subsystem issues that need to be tackled immediately. These messages indicate a potential 'bottlebeck' in physical I/O processing and under certain circumstances may cause corruption.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
November 19, 2010 at 2:23 pm
rudy komacsar - Doctor "X" (11/19/2010)
It is a good thing to initially log and notify on this type of message. However there may be underlying storage subsystem issues that need to be tackled immediately. These messages indicate a potential 'bottlebeck' in physical I/O processing and under certain circumstances may cause corruption.
Thats what we're finding. Still trying to figure out if its the hba card in the sqlserver or the connection to the SAN switch. Odd part about it is it's happening at random times and not necessarily when the system is under heavy load. The activity on the SAN is minimal at best compared to its actual capacity.
November 19, 2010 at 5:43 pm
I need to ask few questions - possibly searching for the answers the root cause(s) of the problem may become more apparent. Well here goes my starting list:
- How often - weekly, daily, hourly or random ?
- Is the count of actual delay requests 1+, 10+, 100 + or 1000+ per file ?
- Is it always the same file(s) ?
- How long has this been happening ?
- Is it tempdb ?
- What RAID level are you using ?
- Have you followed the 'best practices documentation from:
- SAN vendor
- HBA vendor
- Microsoft
Yes, it seems like a lot and detailed. Unfortunately it took us months to figure out out issues on one particular application, database and SAN.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply