//
you're reading...
SQL Server

I/O Taking Longer Then 15 Seconds (SQL MSG 833)

Recently I have been having serious SAN issues. When looking at the usual suspects for slow downs (excessive locking, blocking, wait states, Deadlocks, high CPU) all looks good, However, upon examination of the SQL error and application event logs I immediately noticed error messages similar to the following:

SQL Server has encountered 523 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [g:\log\filename.ndf] in database [dbname] (18).  The OS file handle is 0x0000000000000E14.  The offset of the latest long I/O is: 0x000002cef4c000

That would certainly explain the slow downs and application timeouts. The following code will allow you the set up a custom alert for SQL Server message id 833 (found in the application event log).

USE [msdb]

GO

EXEC msdb.dbo.sp_add_alert @name=N’833-Alert’, @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’833-Alert’,  @operator_name=N’Your Operator Group’,  @notification_method = 1

GO

As with any script you get off the internet please review for appropriateness in your environment. NJOY and I hope this is helpful.

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: