I/O request taking longer than 15 seconds to complete

We're periodically getting the following error message in the SQL log:
07/27/2007 03:42:19,spid2s,Unknown,SQL Server has encountered 21
occurrence(s) of I/O requests taking longer than 15 seconds to complete on
file [E:\mssql\data\centfwamms_log.ldf] in database [centfwamms] (7). The OS
file handle is 0x000006D4. The offset of the latest long I/O is:
0x000001e856fa00
We reviewed Microsoft support notes and other forum discussion on this which
lead us to believe this might be indicative of a hardware issue. We're
running SQL on an HP ML150 G3, with SBS 2003 Premium R2. We then worked
extensively with HP support and ran a detailed diagnostics routine, 5
iterations, which consumed 12+ hours and interrogated all hardware components
- it came back with no problems.
We're not seeing any errors in the operating system event log. Could this
be an erroneous message within SQL? What else to do?
I've seen this same problem and my suspicions are that the disk defrag is
causing the problem.
It only happens after hours when we've set the defrag to run. It happened
again early this morning on one of our servers but it probably been a
month or so since it happened before that.
Jim
On 2007-07-29, Jeffrey Howard <JeffreyHoward@.discussions.microsoft.com> wrote:
> We're periodically getting the following error message in the SQL log:
> 07/27/2007 03:42:19,spid2s,Unknown,SQL Server has encountered 21
> occurrence(s) of I/O requests taking longer than 15 seconds to complete on
> file [E:\mssql\data\centfwamms_log.ldf] in database [centfwamms] (7). The OS
> file handle is 0x000006D4. The offset of the latest long I/O is:
> 0x000001e856fa00
> We reviewed Microsoft support notes and other forum discussion on this which
> lead us to believe this might be indicative of a hardware issue. We're
> running SQL on an HP ML150 G3, with SBS 2003 Premium R2. We then worked
> extensively with HP support and ran a detailed diagnostics routine, 5
> iterations, which consumed 12+ hours and interrogated all hardware components
> - it came back with no problems.
> We're not seeing any errors in the operating system event log. Could this
> be an erroneous message within SQL? What else to do?
|||What does the file stats look like? Does it indicate you have issues
reading or writing to your data or log files? What do your disk queues look
like when this happens on that drive? Are log files the only files on that
physical drive array or is it shared?
Andrew J. Kelly SQL MVP
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:C63FFFEE-84E6-4687-984A-1ECE699ED679@.microsoft.com...
> We're periodically getting the following error message in the SQL log:
> 07/27/2007 03:42:19,spid2s,Unknown,SQL Server has encountered 21
> occurrence(s) of I/O requests taking longer than 15 seconds to complete on
> file [E:\mssql\data\centfwamms_log.ldf] in database [centfwamms] (7). The
> OS
> file handle is 0x000006D4. The offset of the latest long I/O is:
> 0x000001e856fa00
> We reviewed Microsoft support notes and other forum discussion on this
> which
> lead us to believe this might be indicative of a hardware issue. We're
> running SQL on an HP ML150 G3, with SBS 2003 Premium R2. We then worked
> extensively with HP support and ran a detailed diagnostics routine, 5
> iterations, which consumed 12+ hours and interrogated all hardware
> components
> - it came back with no problems.
> We're not seeing any errors in the operating system event log. Could this
> be an erroneous message within SQL? What else to do?
|||> We reviewed Microsoft support notes and other forum discussion on this which
In case you have not, I'd suggest you review the notes/articles by Bob Dorr
on this subject, especially this one:
http://www.microsoft.com/technet/prodtechnol/sql/2005/diagandcorrecterrs.mspx
Linchi
"Jeffrey Howard" wrote:

> We're periodically getting the following error message in the SQL log:
> 07/27/2007 03:42:19,spid2s,Unknown,SQL Server has encountered 21
> occurrence(s) of I/O requests taking longer than 15 seconds to complete on
> file [E:\mssql\data\centfwamms_log.ldf] in database [centfwamms] (7). The OS
> file handle is 0x000006D4. The offset of the latest long I/O is:
> 0x000001e856fa00
> We reviewed Microsoft support notes and other forum discussion on this which
> lead us to believe this might be indicative of a hardware issue. We're
> running SQL on an HP ML150 G3, with SBS 2003 Premium R2. We then worked
> extensively with HP support and ran a detailed diagnostics routine, 5
> iterations, which consumed 12+ hours and interrogated all hardware components
> - it came back with no problems.
> We're not seeing any errors in the operating system event log. Could this
> be an erroneous message within SQL? What else to do?
|||Linchi:
Thank you - we reviewed the article this morning and it is not applicable to
this situation.
Jeff
"Linchi Shea" wrote:
[vbcol=seagreen]
> In case you have not, I'd suggest you review the notes/articles by Bob Dorr
> on this subject, especially this one:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/diagandcorrecterrs.mspx
> Linchi
> "Jeffrey Howard" wrote:
|||Jim:
Thank you. Our disk has 97% space availability. We did run defrag after
getting your feedback but unfortunately that didn't resolve the problem
because we more of these messages afterwards.
Jeff
"Jim Holcomb" wrote:

> I've seen this same problem and my suspicions are that the disk defrag is
> causing the problem.
> It only happens after hours when we've set the defrag to run. It happened
> again early this morning on one of our servers but it probably been a
> month or so since it happened before that.
> Jim
> On 2007-07-29, Jeffrey Howard <JeffreyHoward@.discussions.microsoft.com> wrote:
>
|||Andrew:
Thank you. We're not seeing any other issues; this system is used very
lightly by 1-2 users during the day time, but has a number of batch processes
which run overnight. We rarely see these messages refer to daytime
operations. We've got 97% disk space available; the system's sole
responsibility is SQL; the drive where the log file and database are stored
is shared, but not that directory.
Jeff
"Andrew J. Kelly" wrote:

> What does the file stats look like? Does it indicate you have issues
> reading or writing to your data or log files? What do your disk queues look
> like when this happens on that drive? Are log files the only files on that
> physical drive array or is it shared?
> --
> Andrew J. Kelly SQL MVP
> "Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
> news:C63FFFEE-84E6-4687-984A-1ECE699ED679@.microsoft.com...
>
>
|||> the drive where the log file and database are stored
> is shared, but not that directory.
Consider moving log files to another drive, preferably RAID 1 or 10.
Separating data and log files a SQL Server Best Practice because it prevents
data file I/O from interfering with the transaction log writes and also
provides more recovery options. Moving the log files to a different drive
will likely mitigate the I/O warning messages too.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:E1CAA072-FDCE-4033-95E4-D9E0C5DF677E@.microsoft.com...[vbcol=seagreen]
> Andrew:
> Thank you. We're not seeing any other issues; this system is used very
> lightly by 1-2 users during the day time, but has a number of batch
> processes
> which run overnight. We rarely see these messages refer to daytime
> operations. We've got 97% disk space available; the system's sole
> responsibility is SQL; the drive where the log file and database are
> stored
> is shared, but not that directory.
> Jeff
>
> "Andrew J. Kelly" wrote:
|||So I take it you are saying this happens most when the batch jobs are being
run. If so then this is a prime candidate for seeing issues like this.
Anytime you do heavy write activity in SQL Server you will always have
issues to some degree with the data and log files on the same physical disk
or disk array. If this is a single disk drive and not a disk array you can
easily get I/O contention. You should think about adding another disk or
disk array as Dan mentioned to house the log files and nothing else.
Andrew J. Kelly SQL MVP
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:E1CAA072-FDCE-4033-95E4-D9E0C5DF677E@.microsoft.com...[vbcol=seagreen]
> Andrew:
> Thank you. We're not seeing any other issues; this system is used very
> lightly by 1-2 users during the day time, but has a number of batch
> processes
> which run overnight. We rarely see these messages refer to daytime
> operations. We've got 97% disk space available; the system's sole
> responsibility is SQL; the drive where the log file and database are
> stored
> is shared, but not that directory.
> Jeff
>
> "Andrew J. Kelly" wrote:
|||We moved the log file to a different RAID drive but found that while it
lessened the number of these messages considerably, it did not eliminate them.
"Dan Guzman" wrote:

> Consider moving log files to another drive, preferably RAID 1 or 10.
> Separating data and log files a SQL Server Best Practice because it prevents
> data file I/O from interfering with the transaction log writes and also
> provides more recovery options. Moving the log files to a different drive
> will likely mitigate the I/O warning messages too.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
> news:E1CAA072-FDCE-4033-95E4-D9E0C5DF677E@.microsoft.com...
>