August 28, 2014 at 3:48 am
We use a third party to host our sql servers. All our data and log files are on separate discs - S for data, L for Logs.
We use replication and on the Distribution server all the databases are using simple recovery model.
In the error log on that server I see:
SQL Server has encountered 1 occurrences of I/O requests taking longer than 15 seconds to complete on file : L[\Path to file\distribution.LDF] in database distribution
I run
SELECT backup_finish_date, backup_size/1024.0/1024.0 AS Backup_Size_MB,database_name AS DataBase_Log FROM msdb..backupset
WHERE type='l' and backup_size > 2 and database_name = 'distribution'
AND backup_finish_date BETWEEN '20140827 09:00:00.000' AND '20140827 19:30:00
and get nothing returned - this makes sense as all the databases are in Simple recovery so no log backups are occurring. What is causing the I/O request taking longer than 15seconds - would this be something to do with checkpoints on the distribution db.
The above query shows historic log backups, is there a query i can run to check the historic log flushes\checkpoints ?
August 28, 2014 at 8:41 am
Are you seeing lots of errors like this in the log, or was it just a one-off?
August 28, 2014 at 8:54 am
PearlJammer1 (8/28/2014)
We use a third party to host our sql servers. All our data and log files are on separate discs - S for data, L for Logs.We use replication and on the Distribution server all the databases are using simple recovery model.
In the error log on that server I see:
SQL Server has encountered 1 occurrences of I/O requests taking longer than 15 seconds to complete on file : L[\Path to file\distribution.LDF] in database distribution
Where do the separate disks reside, I'm guessing they're not locally attached but provided by a storage area network.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 29, 2014 at 9:03 am
Sometimes I'll see these errors only once in day, other days it can be quite a lot more - maybe 5 - 10 times.
We are using a hosted environment and which is no doubt shared storage on a SAN.
Worth noting as well, when I look in sql monitor and look at the disc io read time for our data drive S , I see the same spikes happening on all our servers at roughly the same time.
We have approx 15 servers each with their own default instance - no other instances installed.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply