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
See if this helps http://support.microsoft.com/kb/897284 "Suchi" 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
Also http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/21/642314.aspx "Uri Dimant" wrote in message news:e8XVkHw%23JHA.5092@TK2MSFTNGP03.phx.gbl... > See if this helps > http://support.microsoft.com/kb/897284 > > > > > "Suchi" 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 > >
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 "Suchi" 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
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 "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
