help emergency: complete

Showing posts with label complete. Show all posts

Wednesday, March 28, 2012

I/O requests taking longer than 15 seconds to complete

We get below error in MS SQL 2005 server which is fail over virtual server on
windows 2000 advanced servers clusterring.
SQL Server has encountered 16 occurrence(s) of I/O requests taking longer
than 15 seconds to complete on file [Y:\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2). The OS file
handle is 0x00000768. The offset of the latest long I/O is: 0x00000000652000
.
what is the cause and solution ?
NOTE:- 1- there are no sql 2005 service packs installed.
2- all HP Servers hardwares and HP SAN Storage drivers and firmwares are
updated upto date .
3- this error is generated on application peak hours .This blog entry explains the error and offers suggestions
for addressing the issue:
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/21/642314.aspx
-Sue
On Mon, 19 Mar 2007 03:02:11 -0700, maslooki
<maslooki@.discussions.microsoft.com> wrote:
>We get below error in MS SQL 2005 server which is fail over virtual server on
>windows 2000 advanced servers clusterring.
>SQL Server has encountered 16 occurrence(s) of I/O requests taking longer
>than 15 seconds to complete on file [Y:\Microsoft SQL
>Server\MSSQL.1\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2). The OS file
>handle is 0x00000768. The offset of the latest long I/O is: 0x00000000652000
>.
>what is the cause and solution ?
>NOTE:- 1- there are no sql 2005 service packs installed.
>2- all HP Servers hardwares and HP SAN Storage drivers and firmwares are
>updated upto date .
>3- this error is generated on application peak hours .
>|||Thank you MR.Sue Hoegemeier for reply
We also get the below error on the application side :-
" Transaction (Process ID 57) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Return the transaction. "
Please again what is the cause and solution for this error ?
Note :- This happens on peak hours when they generate Reports and upload
transactions .
Thanks for cooperation
"Sue Hoegemeier" wrote:
> This blog entry explains the error and offers suggestions
> for addressing the issue:
> http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/21/642314.aspx
> -Sue
> On Mon, 19 Mar 2007 03:02:11 -0700, maslooki
> <maslooki@.discussions.microsoft.com> wrote:
> >We get below error in MS SQL 2005 server which is fail over virtual server on
> >windows 2000 advanced servers clusterring.
> >
> >SQL Server has encountered 16 occurrence(s) of I/O requests taking longer
> >than 15 seconds to complete on file [Y:\Microsoft SQL
> >Server\MSSQL.1\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2). The OS file
> >handle is 0x00000768. The offset of the latest long I/O is: 0x00000000652000
> >.
> >
> >what is the cause and solution ?
> >
> >NOTE:- 1- there are no sql 2005 service packs installed.
> >2- all HP Servers hardwares and HP SAN Storage drivers and firmwares are
> >updated upto date .
> >3- this error is generated on application peak hours .
> >
>|||You may consider ensuring that your reporting queries (I assume SELECT only)
use snapshot isolation level. Better still would be to replicate or mirror
to a reporting server which will handle the queries independent of the true
OLTP activity.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"maslooki" <maslooki@.discussions.microsoft.com> wrote in message
news:1D25A567-53FF-4667-9D3D-39F50D1F7FD7@.microsoft.com...
> Thank you MR.Sue Hoegemeier for reply
> We also get the below error on the application side :-
> " Transaction (Process ID 57) was deadlocked on lock resources with
> another
> process and has been chosen as the deadlock victim. Return the
> transaction. "
> Please again what is the cause and solution for this error ?
> Note :- This happens on peak hours when they generate Reports and upload
> transactions .
> Thanks for cooperation
> "Sue Hoegemeier" wrote:
>> This blog entry explains the error and offers suggestions
>> for addressing the issue:
>> http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/21/642314.aspx
>> -Sue
>> On Mon, 19 Mar 2007 03:02:11 -0700, maslooki
>> <maslooki@.discussions.microsoft.com> wrote:
>> >We get below error in MS SQL 2005 server which is fail over virtual
>> >server on
>> >windows 2000 advanced servers clusterring.
>> >
>> >SQL Server has encountered 16 occurrence(s) of I/O requests taking
>> >longer
>> >than 15 seconds to complete on file [Y:\Microsoft SQL
>> >Server\MSSQL.1\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2). The OS
>> >file
>> >handle is 0x00000768. The offset of the latest long I/O is:
>> >0x00000000652000
>> >.
>> >
>> >what is the cause and solution ?
>> >
>> >NOTE:- 1- there are no sql 2005 service packs installed.
>> >2- all HP Servers hardwares and HP SAN Storage drivers and firmwares are
>> >updated upto date .
>> >3- this error is generated on application peak hours .
>> >
>>|||Thank you all,
Our programmers are working on it.
"Aaron Bertrand [SQL Server MVP]" wrote:
> You may consider ensuring that your reporting queries (I assume SELECT only)
> use snapshot isolation level. Better still would be to replicate or mirror
> to a reporting server which will handle the queries independent of the true
> OLTP activity.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "maslooki" <maslooki@.discussions.microsoft.com> wrote in message
> news:1D25A567-53FF-4667-9D3D-39F50D1F7FD7@.microsoft.com...
> > Thank you MR.Sue Hoegemeier for reply
> >
> > We also get the below error on the application side :-
> > " Transaction (Process ID 57) was deadlocked on lock resources with
> > another
> > process and has been chosen as the deadlock victim. Return the
> > transaction. "
> >
> > Please again what is the cause and solution for this error ?
> >
> > Note :- This happens on peak hours when they generate Reports and upload
> > transactions .
> >
> > Thanks for cooperation
> >
> > "Sue Hoegemeier" wrote:
> >
> >> This blog entry explains the error and offers suggestions
> >> for addressing the issue:
> >> http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/21/642314.aspx
> >>
> >> -Sue
> >>
> >> On Mon, 19 Mar 2007 03:02:11 -0700, maslooki
> >> <maslooki@.discussions.microsoft.com> wrote:
> >>
> >> >We get below error in MS SQL 2005 server which is fail over virtual
> >> >server on
> >> >windows 2000 advanced servers clusterring.
> >> >
> >> >SQL Server has encountered 16 occurrence(s) of I/O requests taking
> >> >longer
> >> >than 15 seconds to complete on file [Y:\Microsoft SQL
> >> >Server\MSSQL.1\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2). The OS
> >> >file
> >> >handle is 0x00000768. The offset of the latest long I/O is:
> >> >0x00000000652000
> >> >.
> >> >
> >> >what is the cause and solution ?
> >> >
> >> >NOTE:- 1- there are no sql 2005 service packs installed.
> >> >2- all HP Servers hardwares and HP SAN Storage drivers and firmwares are
> >> >updated upto date .
> >> >3- this error is generated on application peak hours .
> >> >
> >>
> >>
>
>

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:
> > 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?|||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:
> > 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?
>|||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...
> > 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?
>
>|||> 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...
> 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...
>> > 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?
>>|||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...
> 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...
>> > 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?
>>|||Make sure your batch jobs are set to not overlap at all to allow most
breathing room for the server.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:E1CAA072-FDCE-4033-95E4-D9E0C5DF677E@.microsoft.com...
> 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...
>> > 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 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:
> > 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...
> > 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...
> >> > 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 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.
I'm glad you're making progress. The bottom line is that you'll need to
either reduce the batch workload or increase disk I/O capacity.
How are the disks configured now (RAID and number of spindles)? Do you have
any other processes besides SQL Server that may be contributing to the I/O
workload?
Have you reviewed the batch schedule as TheSQLGuru and Andrew suggested?
Since you are still getting the warning, it appears you are attempting more
concurrent work than your I/O subsystem and server can handle. In that
case, you'll probably improve batch throughput and reduce the warning
messages by adjusting the schedule. Running SQL queries serially instead of
concurrently can also improve buffer efficiency and reduce I/O workload.
Aside from changing the batch schedule, it's possible that application
tuning or redesign can reduce I/O demand. Check I/O intensive queries to
see if there is room for improvement. Adding more disks (and perhaps RAID
changes) will increase I/O capacity but I don't recommend throwing hardware
at this problem unless you are certain it's a supply and demand issue.
Also, see
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/21/642314.aspx
and monitor the disk performance counters during batch processing.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:D20393B9-2554-46A1-A1FD-509624F07A5A@.microsoft.com...
> 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:
>> > 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...
>> > 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...
>> >> > 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?
>> >>
>> >>
>> >>|||Finally how did you resolve this issue?
TIA,
--
Manoj Kumar
"Jeffrey Howard" wrote:
> Linchi:
> Thank you - we reviewed the article this morning and it is not applicable to
> this situation.
> Jeff
> "Linchi Shea" wrote:
> > > 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?|||Please post if the issue is resolved and what steps were taking to
resolve the issue.

I/O request taking longer than 15 seconds to complete

I/O error 1450, Error 823 during heavy loads

Hi,

We're experiencing the following error regularly:
"I/O error 1450(Insufficient system resources
exist to complete the requested service.)"

We are running SQL2000 with 2GB memory, Windows 2000 AS, 4 CPUs. We are using a SAN storage connected by 2 Fibre cards. The databases range from 10GB to 400GB in size for decision support applications.

Although it seems clear that the disk subsystem is causing this error, our hosting party is blaming the application layer for this behaviour.

From the SQL server log:
I/O error 1450(Insufficient system resources exist to complete the requested service.) detected during read at offset 0x00000350860000 in file 'O:\DATA\SA_Data.MDF'..
Error: 823, Severity: 24, State: 2
I/O error 1450(Insufficient system resources exist to complete the requested service.) detected during read at offset 0x00000350862000 in file 'O:\DATA\SA_Data.MDF'..
Error: 823, Severity: 24, State: 2

From the event log:
[..]
dmio: Harddisk37 read error at block 23192007: status 0xc000009a
dmio: Harddisk35 read error at block 23192135: status 0xc000009a
dmio: Harddisk36 read error at block 23192127: status 0xc000009a
dmio: Harddisk36 read error at block 23192263: status 0xc000009a
[etc]
dmio: Disk Harddisk31 block 23193791 (mountpoint O:): Uncorrectable read error
[..]

Does anyone have any suggestions??

Desmondcheck this KB
http://support.microsoft.com/default.aspx?scid=kb;EN-US;274310|||Thanks, but this KB article applies to MS SQL Server 7; we're on SQL Server 2000, SP3.|||skipped that part sorry.|||Where should we look first? At the application, or the hardware infrastructure?

We have 'dynamic disks' configured on Windows, and use about 60+ LUNs.
Partly configured as stripeset on OS level (Software RAID) and partly grouped in SQL server filegroups, so that we maximize performance on the SAN storage.

It's is a decision support datawarehouse system, with a 400GB as the largest database.

Any suggestion is appreciated!|||You should do all the hardware diagnostics you can on each piece of hardware between the CPU and the disk (fibre cards, controller, disks, and even the fibre cable if the previous ones all come up clean). Error 823 is a very low level error, and can not be caused by an errant stored procedure, or query. Given even the limited amount of errors displayed here, I am sure Microsoft would be willing to participate in a conference call with you and the hosting company to review the errors, and get them to do the hardware diagnostics.|||Right now, the most likely root cause would be the low level of System Page Table Entries (SystemPTEs). This should, however, show up in the even log like this:
Stop 0x0000003F NO_MORE_SYSTEM_PTES
Stop 0x000000D8 DRIVER_USED_EXCESSIVE_PTES
which does not.

Looking at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;828339
it should be caused by a lower level OS-related problem, possibly to do with
http://support.microsoft.com/default.aspx?scid=kb;en-us;555068

Please also refer to
http://support.microsoft.com/default.aspx?scid=kb;EN-US;247904
which describes what resources fall(System Page Table Entry , SystemPTE) below a certain threshold.
We are now monitoring our SystemPTE levels while the box is on heavy loads.

I'll keep you posted.

I/O error 1450(Insufficient system resources exist

Hi,
We're experiencing the following error on a frequent basis
recently - "I/O error 1450(Insufficient system resources
exist to complete the requested service.)"
We are running SQL2000 with AWE memory configured for 6128
MB, with a machine equipped with 8 GB on Windows 2000.
We are also using a SAN. The databases are from 5 GB to 80
GB in size for decision support applications.
Unfortunately, I don't see any events stating that a disk
controller has failed, or anything hardware related.
Does anyone have any suggestions?
Thanks.
Larrylarry, we are experiencing the same problem. did you get yours done?sql

Wednesday, March 28, 2012

I/O requests taking longer than 15 seconds to complete

I/O requests taking longer than 15 seconds to complete

I/O requests taking longer than 15 seconds to complete

I/O request taking longer than 15 seconds to complete

I/O request taking longer than 15 seconds to complete

I/O request taking longer than 15 seconds to complete

I/O error 1450, Error 823 during heavy loads

I/O error 1450(Insufficient system resources exist

help emergency

Blog Archive

About Me