June 3, 2009 at 12:29 pm
Hi,
I have noticed the below message in the error log:
2009-05-27 20:22:24.95 spid2s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [d:\Program Files\Microsoft SQL Server\MSSQL\data\msdblog.ldf] in database [msdb] (4). The OS file handle is 0x00000804. The offset of the latest long I/O is: 0x000000005d9c00
Is this critical message?
it it caused because of the SPID2s?
is something wrong with msdb?
if it is an issue to work on, how shoul I diagnose the cause?
thanks
June 3, 2009 at 3:52 pm
Hi rambilla4
I have not seen this error on MSDB before but when I get it on an app database it is trouble.
The message is telling you that an I/O request to the MSDB transaction log has not completed in 15 seconds and these should normally complete in less than 15milliseconds.
You can use perfmon to monitor physical and logical disk stats (Reads, Writes, I/Os, Idle) and Profiler to monitor query performance.
I would not ignore this error since it may indicate a real problem in your I/O subsystem.
June 3, 2009 at 4:46 pm
rambilla4 (6/3/2009)
Hi,I have noticed the below message in the error log:
2009-05-27 20:22:24.95 spid2s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [d:\Program Files\Microsoft SQL Server\MSSQL\data\msdblog.ldf] in database [msdb] (4). The OS file handle is 0x00000804. The offset of the latest long I/O is: 0x000000005d9c00
Hi Rambilla4,
Your sub disk system is stressed\overloaded.
Please gather stats for mentioned counters under physical disk avg disk sec\read, avg disk sec\write and avg disk sec\transfer.
Value
Value of this counters at any given time should be less than 20 MS. If there is one time high that is acceptable, if values are continuously highly, you should get in touch with your storage team to figureout and resolve this issue.
Is SQL Server database files are placed on SAN or locally?
If SAN, is SAN dedicated to SQL Server or being shared by various other applications like file server\exchange etc.
If SAN being shared then it has to be figured out what is causing that these messages recorded in error log.
If SQL server database files are placed on local disks, considering sql server is the only application that is running check what is causing this to happeen. Is any database maintenace activity like backups,reindexing or dbcc checks i? Also check which RAID level is being used.
Hope upon checking this you may figure it out what is causing the disk subsystem overloaded \ stressed.
June 3, 2009 at 9:52 pm
We have all the drives including C drive on SAN.
If SAN being shared then it has to be figured out what is causing that these messages recorded in error log.
the above mentioned error log is on SAN side or you taking about sql server error log?
if it is sql server, I did not see any errors other than the one I mentioned.
it is is SAN, please let me know so that I can ask the SAN admin to check that error log.
thanks
June 3, 2009 at 10:41 pm
Hi,
I would suggest you to check the complete error logs so that we can see whether this is the only message that is logged or we have couple of others as well which got logged time to time. If this message is logged just 1 time then there might because of some heavy work or anything it take some time in order to perform I/O but if this messages is coming repeateadly then yes we need to check on the disk subsystem side i.e. SAN In your case.
You can use PERFMON and selcet below counters.
Perfmonace Object:
Physical Disk
Counters:
Avg. Dik Sec/Read
Avg. Dik Sec/Write
Avg. Disk Sec/Transfer
Avg. Disk Queue Length
These values should be less than 20 MS for individual disk.
Let me know in case you need more help.
Regards
GURSETHI
June 4, 2009 at 10:25 am
rambilla4 (6/3/2009)
We have all the drives including C drive on SAN.If SAN being shared then it has to be figured out what is causing that these messages recorded in error log.
the above mentioned error log is on SAN side or you taking about sql server error log?
if it is sql server, I did not see any errors other than the one I mentioned.
it is is SAN, please let me know so that I can ask the SAN admin to check that error log.
thanks
I am talking about the error message that you have posted earlier and this will be recorded in sql server error log.
If you see these messages in sql server error log, check with your SAN admin about its performance.
June 4, 2009 at 12:13 pm
Hi,
Perfmonace Object:
Physical Disk
Counters:
Avg. Dik Sec/Read
Avg. Dik Sec/Write
Avg. Disk Sec/Transfer
Avg. Disk Queue Length
These values should be less than 20 MS for individual disk.
I had set up the following counters and Iam posting some sample data. But I am unable to figure out that reading were <20ms?
how can I make sure it is less than or greater than 20ms from the below sample data? plz see the attachement for the values
June 4, 2009 at 12:29 pm
[Quote]
I had set up the following counters and Iam posting some sample data. But I am unable to figure out that reading were <20ms?
how can I make sure it is less than or greater than 20ms from the below sample data? plz see the attachement for the values
[/quote]
20 MS translates to 0.020
Based on sample data that you have sent, writes are taking little bit more time.
June 15, 2009 at 11:02 am
SQL Server has encountered 446 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [d:\Program Files\Microsoft SQL Server\MSSQL\Data\ABC_QA.mdf] in database [ABC_QA] (7). The OS file handle is 0x000007A4. The offset of the latest long I/O is: 0x000000510c0000
I'm getting this message during the weekly Rebuild index job is running. Is this fine to get this message while index rebuilds for all the databases or this error should not occur at all???
While rebuilding indexes, I'm getting the below error in the event viewer:
Event Type: Error
Event Source: Report Server Windows Service (MSSQLSERVER)
Event Category: Management
Event ID: 107
Date: 6/6/2009
Time: 8:20:56 AM
User: N/A
Computer: ABC
Description:
Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database.
Do, I need to exclude the Report Server and ReportServerTempDb from index rebuild job?
May 30, 2013 at 7:54 am
I am seeing the same error on one of the prod SQL server. Gathered performance stats on disk attached to SAN. The numbers were not alarming (less than 20 ms). Any thoughts?
Error: SQL Server has encountered 2 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [MSDBLog.ldf] in database [msdb] . The OS file handle is XXXX. The offset of the latest long I/O is: XXXX
[font=Arial]Prakash Bhojegowda MCSE, MCSA (SQL2012)[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply