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
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.
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 enableTF 1118 and 1117
. Also, make sure you have properly sized your data and log files to prevent Autogrowths.