SQLhas encountered 2 occurrence(s) of IO requests taking longer th

2 Jul 2009 10:20 AM

Suchi

HI All,

Environment:

WIN2003 Enterprise Edition
SQL2000 with SP4(cluster with 2 nodes)

I see below messages in SQL logs almost everyday for tempdb and for other
few databases. They are more for tempdb. The Tempdb is on seperate drives.

SQL Server has encountered 1 occurrence(s) of IO requests taking longer than
15 seconds to complete on file [V:\MSSQL\Data\tempdata7.NDF] in database
[tempdb] (2).
The OS file handle is 0x000004B1. The offset of the late    st long IO is:
0x00000005acb90000    1
2009-07-02 01:47:31.31 spid1     SQL Server has encountered 1 occurrence(s)
of IO requests taking longer than 15 seconds to complete on file
[X:\mssql\data\database1.MDF] in database [database1] (12). The OS file
handle is 0x00000628. The off    0
set of the latest long IO is: 0x0000000da88000    1
2009-07-02 01:47:31.31 spid1     SQL Server has encountered 1 occurrence(s)
of IO requests taking longer than 15 seconds to complete on file
[P:\mssql\data\tempdata1.ndf] in database [tempdb] (2). The OS file handle
is 0x00000550. The offset of the late    0
st long IO is: 0x000000297b4000    1

We have total 10 tempdb files(8 data and 2 log) spread on 2 Drives.Our
windows team checked the health of the physical drives and they said no
issues with the disk drives.The tempdb is auto growth is turned on .

How i resolve this issue? Any ideas?

Thanks in Advance
Suchi

2 Jul 2009 10:43 AM

Uri Dimant

See if this helps
http://support.microsoft.com/kb/897284

Show quoteHide quote

"Suchi" <Su***@discussions.microsoft.com> wrote in message
news:BBA56E61-81BB-4B07-8F9E-D3D193FFC474@microsoft.com...
> HI All,
>
> Environment:
>
> WIN2003 Enterprise Edition
> SQL2000 with SP4(cluster with 2 nodes)
>
> I see below messages in SQL logs almost everyday for tempdb and for other
> few databases. They are more for tempdb. The Tempdb is on seperate drives.
>
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
> than
> 15 seconds to complete on file [V:\MSSQL\Data\tempdata7.NDF] in database
> [tempdb] (2).
> The OS file handle is 0x000004B1. The offset of the late st long IO is:
> 0x00000005acb90000 1
> 2009-07-02 01:47:31.31 spid1 SQL Server has encountered 1
> occurrence(s)
> of IO requests taking longer than 15 seconds to complete on file
> [X:\mssql\data\database1.MDF] in database [database1] (12). The OS file
> handle is 0x00000628. The off 0
> set of the latest long IO is: 0x0000000da88000 1
> 2009-07-02 01:47:31.31 spid1 SQL Server has encountered 1
> occurrence(s)
> of IO requests taking longer than 15 seconds to complete on file
> [P:\mssql\data\tempdata1.ndf] in database [tempdb] (2). The OS file
> handle
> is 0x00000550. The offset of the late 0
> st long IO is: 0x000000297b4000 1
>
>
> We have total 10 tempdb files(8 data and 2 log) spread on 2 Drives.Our
> windows team checked the health of the physical drives and they said no
> issues with the disk drives.The tempdb is auto growth is turned on .
>
> How i resolve this issue? Any ideas?
>
> Thanks in Advance
> Suchi

2 Jul 2009 10:57 AM

Uri Dimant

Also
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/21/642314.aspx

Show quoteHide quote

"Uri Dimant" <u***@iscar.co.il> wrote in message
news:e8XVkHw%23JHA.5092@TK2MSFTNGP03.phx.gbl...
> See if this helps
> http://support.microsoft.com/kb/897284
>
>
>
>
> "Suchi" <Su***@discussions.microsoft.com> wrote in message
> news:BBA56E61-81BB-4B07-8F9E-D3D193FFC474@microsoft.com...
>> HI All,
>>
>> Environment:
>>
>> WIN2003 Enterprise Edition
>> SQL2000 with SP4(cluster with 2 nodes)
>>
>> I see below messages in SQL logs almost everyday for tempdb and for other
>> few databases. They are more for tempdb. The Tempdb is on seperate
>> drives.
>>
>> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
>> than
>> 15 seconds to complete on file [V:\MSSQL\Data\tempdata7.NDF] in database
>> [tempdb] (2).
>> The OS file handle is 0x000004B1. The offset of the late st long IO is:
>> 0x00000005acb90000 1
>> 2009-07-02 01:47:31.31 spid1 SQL Server has encountered 1
>> occurrence(s)
>> of IO requests taking longer than 15 seconds to complete on file
>> [X:\mssql\data\database1.MDF] in database [database1] (12). The OS file
>> handle is 0x00000628. The off 0
>> set of the latest long IO is: 0x0000000da88000 1
>> 2009-07-02 01:47:31.31 spid1 SQL Server has encountered 1
>> occurrence(s)
>> of IO requests taking longer than 15 seconds to complete on file
>> [P:\mssql\data\tempdata1.ndf] in database [tempdb] (2). The OS file
>> handle
>> is 0x00000550. The offset of the late 0
>> st long IO is: 0x000000297b4000 1
>>
>>
>> We have total 10 tempdb files(8 data and 2 log) spread on 2 Drives.Our
>> windows team checked the health of the physical drives and they said no
>> issues with the disk drives.The tempdb is auto growth is turned on .
>>
>> How i resolve this issue? Any ideas?
>>
>> Thanks in Advance
>> Suchi
>
>

2 Jul 2009 7:18 PM

TheSQLGuru

1) get rid of one of the tlog files. No database should have more than 1
tlog file

2) you have too many files on single spindles (assuming they are single
spindles). Even if there are several drives I would say 8 is too many.
Drop it back to 1 or perhaps 2 per disk. Too many files makes the heads
seek around and you wind up getting way too many access/seek delays per IO.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

Show quoteHide quote

"Suchi" <Su***@discussions.microsoft.com> wrote in message
news:BBA56E61-81BB-4B07-8F9E-D3D193FFC474@microsoft.com...
>
> HI All,
>
> Environment:
>
> WIN2003 Enterprise Edition
> SQL2000 with SP4(cluster with 2 nodes)
>
> I see below messages in SQL logs almost everyday for tempdb and for other
> few databases. They are more for tempdb. The Tempdb is on seperate drives.
>
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
> than
> 15 seconds to complete on file [V:\MSSQL\Data\tempdata7.NDF] in database
> [tempdb] (2).
> The OS file handle is 0x000004B1. The offset of the late st long IO is:
> 0x00000005acb90000 1
> 2009-07-02 01:47:31.31 spid1 SQL Server has encountered 1
> occurrence(s)
> of IO requests taking longer than 15 seconds to complete on file
> [X:\mssql\data\database1.MDF] in database [database1] (12). The OS file
> handle is 0x00000628. The off 0
> set of the latest long IO is: 0x0000000da88000 1
> 2009-07-02 01:47:31.31 spid1 SQL Server has encountered 1
> occurrence(s)
> of IO requests taking longer than 15 seconds to complete on file
> [P:\mssql\data\tempdata1.ndf] in database [tempdb] (2). The OS file
> handle
> is 0x00000550. The offset of the late 0
> st long IO is: 0x000000297b4000 1
>
>
> We have total 10 tempdb files(8 data and 2 log) spread on 2 Drives.Our
> windows team checked the health of the physical drives and they said no
> issues with the disk drives.The tempdb is auto growth is turned on .
>
> How i resolve this issue? Any ideas?
>
> Thanks in Advance
> Suchi

2 Jul 2009 7:45 PM

Linchi Shea

I've had a decent share of these 833 errors. In almost all cases, this is a
storage issue. If I were you, I'd insist they look more closely at the
storage.

Linchi

Show quoteHide quote

"Suchi" wrote:

> HI All,
>
> Environment:
>
> WIN2003 Enterprise Edition
> SQL2000 with SP4(cluster with 2 nodes)
>
> I see below messages in SQL logs almost everyday for tempdb and for other
> few databases. They are more for tempdb. The Tempdb is on seperate drives.
>
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer than
> 15 seconds to complete on file [V:\MSSQL\Data\tempdata7.NDF] in database
> [tempdb] (2).
> The OS file handle is 0x000004B1. The offset of the late    st long IO is:
> 0x00000005acb90000    1
> 2009-07-02 01:47:31.31 spid1     SQL Server has encountered 1 occurrence(s)
> of IO requests taking longer than 15 seconds to complete on file
> [X:\mssql\data\database1.MDF] in database [database1] (12). The OS file
> handle is 0x00000628. The off    0
> set of the latest long IO is: 0x0000000da88000    1
> 2009-07-02 01:47:31.31 spid1     SQL Server has encountered 1 occurrence(s)
> of IO requests taking longer than 15 seconds to complete on file
> [P:\mssql\data\tempdata1.ndf] in database [tempdb] (2). The OS file handle
> is 0x00000550. The offset of the late    0
> st long IO is: 0x000000297b4000    1
>
>
> We have total 10 tempdb files(8 data and 2 log) spread on 2 Drives.Our
> windows team checked the health of the physical drives and they said no
> issues with the disk drives.The tempdb is auto growth is turned on .
>
> How i resolve this issue? Any ideas?
>
> Thanks in Advance
> Suchi