6

I am facing issues related to constantly growing log file due to which I am getting error. When I checked SQL Log I found below messages (error log is filled of these messages almost 90%)

SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file

This is happening almost for all databases, including temdb [.mdf and .ndf files] along with that I am getting below messages also

average throughput: 0.34 MB/sec I/O saturation: 196 context switches 1210

last target outstanding: 530 avgWriteLatency 2

FlushCache: cleaned up 6233 bufs with 384 writes in 142370 ms (avoided 99 new dirty bufs) for db 6:0

My temdb size and other database and log file size is big enough.

History:

  • Previous developers had done both database shrinking and log shrinking many times.
  • All database is in compressed mode.

My action plans:

  • I have found that log file initial size is small with 10% growth. I am planning to increase the initial size by 512 MB with 512 MB growth to have reasonable number of VLF's.

    Question 1: Though I will do in non peek hours of business but are there any chances that making these changes can corrupt my database or log file?

    Question 2: Does database compression mode can effect IO operation? If yes, how can I resolve it?

  • I am planning to remove all database and log files from antivirus check.

  • I am planning to change target recovery time to < 1 minute of data base (specifically tempdb and my database)

    Question 3: Is this going to impact my database? I mean flushing buffer and writing to disk should improve performance, right?

    Question 4: I have three data file for tempdb already have 3 data files. As per Microsoft recommendation, do I have to increase my temdb data files/log files? My current processor configuration is mentioned below

enter image description here

Question 5: Am I going in wrong direction? Is there is something which I am missing that should be done to rectify this issue?

Edit Why I believe this is related to Log file size?

If there is an IO issue then read write ability of SQL will decrease, this will result in long running transaction hence cause large log file size.

  • A few things to check on top of your questions: Do you have instant file initialization? Are log and data files on different spindles? – JoseTeixeira May 7 '15 at 13:57
  • I think you should use perfmon to check the overall health of your storage subsystem. My guess from the description so far would be that an increased number of checkpoints (=more IO load) will make the problem worse. – Martin K. May 7 '15 at 14:01
  • Can you clarify All database is in compressed mode ? Are you using SQL Server Data compression - if yes - row or page ? or SQL server data and log files are placed on compress drives ? What service pack of SQL Server 2012 you are using ? 32bit or 64 bit ? Remove sql server binaries, mdf, ndf and ldf files from Anitvirus scan , for tempdb enable TF 1118 and 1117. Also, make sure you have properly sized your data and log files to prevent Autogrowths. – Kin Shah May 7 '15 at 14:07
  • 1
    @Zerotoinfinity don't bother about backupset compression. The log message you receive indicate that buffer flush is going slow. This means writing modified pages from memory to disk is slow. This is connected to the transaction log since each page modification must have a respective transaction log entry but i don't think it is causing this at all. Go step by step and start with the most obvious, disable the AV. Also check if the mdf and ldf files are located on the same physical disc (if yes this could contribute to the problem, depending on the load) – Martin K. May 7 '15 at 16:32
  • 1
    @JoseTeixeira Log files can't use IFI as log files are always 0'd out, as it's possible for the transaction log to look at old data and think it's valid. ZeroToInfinity Do you have DISK READ/SEC and DISK WRITE/SEC metrics from the drives? That will give us the metrics on how long it's taking to conduct 1 write/read operation. I'm curious what they look like. – Ali Razeghi May 7 '15 at 21:01
3

Question 1: Though I will do in non peek hours of business but are there any chances that making these changes can corrupt my database or log file?

No this cannot damage your Log file.

Question 2: Does database compression mode can effect IO operation? If yes, how can I resolve it?

Yes database compression affects I/O and as far as my experience goes it decreases the I/O and is actually benefitial if you look at I/O consumption. I will tell you how.

  1. Compressed data will take fewer pages when stored on disk hence when data is read fewer I/O is required to fetch data in memory.
  2. Again since fewer pages are bought into memory this increases buffer pool availibility.
  3. CPU is what consumed when compression or decompression happens. But in most cases you would find that benefits gained by space and I/O would outweight the CPU consumption
  4. Please remember a compressed page will remain compressed on disk as well as compressed when brought in memory for reading or writing. So no additional work of decompressing in memory

This is excellent article on data compression, its big, but would help you in understanding data compression

You must remove all SQL Server related folder and files from Antivirus check specially if you have McAfee Antivirus.

•I am planning to change target recovery time to < 1 minute of data base (specifically tempdb and my database)

I can only say please leave it to default, I am not sure how recovery time has anything to do with log file and its configuration

Regarding Tempdb make sure you have Data files equal to number of physical cores. Paul has more to say about how many files you can have as per cores IMHO you can start with 4 tempdb files but keep monitoring for contention. Make sure that all tempdb data files have SAME INITIAL SIZE and SAME AUTOGROWTH setting. You can also enable TF 1118 to avoid contention

To check contention you can run below query

select 
session_id, 
wait_duration_ms,   
resource_description
from    sys.dm_os_waiting_tasks 
where   wait_type like 'PAGE%LATCH_%' and 
resource_description like '2:%'

There are some good article for you to read about Tempdb and Contention

  1. Troubleshooting Tempdb Contention by Paul Randal

  2. Recommendation to reduce Allocation contention in Tempdb database

  3. Monitoring Allocation Bottleneck In Tempdb

Edit:

SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file

This message means that a particular session or query requested for I/O to get data from disk but that session had to wait more than 15 sec and after that request was catered. You can guess that 15 sec is threshold value moment this time is crossed message is dumped in errorlog. This MOSTLY means that disk is not able to cope up with I/O request which is getting generated and in turn means disk might be slow. Since you said 90 % of your errorlog is filled with this information I am forced to believe that underlying hardware is slow or might require a firmware upgrade. This Article will help you in understanding the issue and fixing it

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.