July 30, 2009 at 2:35 pm
I am seeing these messages in the sql server log file and I'm not sure what it means. I am not sure if this is a problem or not but it doesn't look right to see these types of messages in the log. See below.
2009-07-28 23:00:37.91 spid2s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [H:\mssql\data\Archer\Archer.mdf] in database [Archer] (5). The OS file handle is 0x0000000000000808. The offset of the latest long I/O is: 0x00000001220000
2009-07-28 23:11:47.88 spid2s SQL Server has encountered 2 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [H:\mssql\data\Archer\Archer.mdf] in database [Archer] (5). The OS file handle is 0x0000000000000808. The offset of the latest long I/O is: 0x00000002f90000
2009-07-28 23:16:47.87 spid2s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [H:\mssql\txlog\Archer\Archer_log.ldf] in database [Archer] (5). The OS file handle is 0x000000000000080C. The offset of the latest long I/O is: 0x00000031a36200
2009-07-28 23:16:47.87 spid2s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [H:\mssql\data\Archer\Archer.mdf] in database [Archer] (5). The OS file handle is 0x0000000000000808. The offset of the latest long I/O is: 0x00000003a00000
2009-07-29 00:50:07.65 spid2s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [H:\mssql\data\MSSQL.1\MSSQL\DATA\templog.ldf] in database [tempdb] (2). The OS file handle is 0x00000000000006A8. The offset of the latest long I/O is: 0x0000000169a000
2009-07-29 00:50:07.65 spid2s SQL Server has encountered 5 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [H:\mssql\txlog\Archer\Archer_log.ldf] in database [Archer] (5). The OS file handle is 0x000000000000080C. The offset of the latest long I/O is: 0x00000033399600
2009-07-29 00:56:17.64 spid2s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [H:\mssql\txlog\Archer\Archer_log.ldf] in database [Archer] (5). The OS file handle is 0x000000000000080C. The offset of the latest long I/O is: 0x000000334b9a00
2009-07-29 00:56:17.64 spid2s SQL Server has encountered 2 occurrence(s) of I/O requests taking longer than 15 second
July 30, 2009 at 3:30 pm
It means pretty much what it says, SQL's detecting IO requests that take more than 15 seconds to complete. That is, the time between SQL issuing an IO request to the Operating system and the time that the IO actually competed was longer than 15 seconds.
It's not good. It indicates that there's very slow performance of the IO subsystem. A single IO request should take milliseconds, not 15+ seconds. It's generally a sign that either your IO subsystem can't handle the load that's been requested of it, or there's unusually high IO activity.
If you're getting these regularly, the first thing I'd suggest you do is separate the data and log files onto different physical drives.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 31, 2009 at 8:14 am
Okay. Thank you Gail. I will be looking now on how to do that. I've never done it before so I will be investigating what the steps should be.
Thanks again.
Patti
July 31, 2009 at 8:23 am
Another thing that can cause this is insufficient memory, or improperly allocated memory. If the server is doing a lot of paging I/O it will impact performance, especially of your database file(s) are on the paging drive.
Also, it may seem obvious but... if you have other processes running on your server can be consuming I/O and CPU bandwidth that will degrade SQL Server performance. Look at the profiler and/or task manager.
The probability of survival is inversely proportional to the angle of arrival.
October 15, 2009 at 12:47 pm
Did you figure out what your problem was? Mine just began to do this as well
October 16, 2009 at 1:03 am
I was facing the same issues on my development enviornment. I seperated the mdf & ldf files on different drives & this solved my issue.
for reference you can read the below article by MICROSOFT
Abhijit - http://abhijitmore.wordpress.com
October 16, 2009 at 6:34 am
Thanks, but I had setup my server up with that configuration when it was new.
October 16, 2009 at 7:19 am
Let me check with the Window's SA's. I believe there was something wrong with the SAN drives. There were also driver messages in the windows event log.
October 16, 2009 at 7:32 am
I remember some time back the same error occured repeatedly and we solved it by Moving the Datafiles of the multiple databases to a seperate drive fior each of them (Of course only Large DB's)
You better try looking for the Drive and see what all Databases are using that drive and plan accordingly.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 16, 2009 at 8:15 am
For us, the SAN drive was shared by other servers that had databases on it. So it isn't as straight forward for us.
October 20, 2009 at 9:20 am
Thanks everyone. My data and log files are already on separate drives and no I don't have a SAN
October 20, 2009 at 9:33 am
Are you in a virtual environment? We had the same issues when our database server was a virtual server, when we switched it to a physical server all of our issues disappeared.
October 20, 2009 at 9:37 am
Nope, no virtual environment either, pretty standard setup
October 21, 2009 at 2:18 am
Any SQL / Windows jobs overlapping to each other or taking longer time than the usual. THsi could be the reason also.
Abhijit - http://abhijitmore.wordpress.com
October 21, 2009 at 10:51 am
There are MANY things that can cause slow IO, although it is often a combination of them that lead to the 15 sec warning (that is REALLY bad, btw, as Gail pointed out). If you don't know much about performance analysis and tuning I HIGHLY recommend you get a professional to help you out. A few minutes/hours with a good tuner can make a HUGE difference. Or you can spend days or even weeks going back and forth on a forum trying to figure out the root cause(s). 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply