28 Februari 2012 12:48Can a continuously growing log file impact queries performance? Has it any connection with the overall database performance?
28 Februari 2012 13:33
It could affect the performance if it is continuously growing. mainly because of the extra I/O on the disk when the LDF file is being expanded. Besides huge LDF files are more complicate to handle.
28 Februari 2012 13:49Penjawab Pertanyaan
Q: Can a continuously growing log file impact queries performance?
A: It depends. As far as SELECT queries are concerned, no. SELECT queries aren't a logged operation and won't cause any changes to the database, they generally depend on a valid and optimized plan. INSERT/UPDATE/DELETE can be impacted. I say can, because they may not necessarily be as it depends if the logging of the operation is what's making the request wait or the growth of the log. The overall SIZE of the log doesn't dictate performance from a query point of view, but a recovery point of view. The only time this isn't the case is if you had a large log file that was set to autogrow by a certain amount, say 10% since that's the default. When a log file grows, all of the new portion of the log must be zeroed out. If we take 10% of a large log, say 50 GB, that's 5 GB which isn't much, but could have a noticeable performance hit.
Q: Has it any connection with the overall database performance?
A: It does. Backup and recovery times can take longer with a larger log file, can but not necessarily will. There are many factors that go into it such as number of vlf (virtual log files), amount of data needing to be backed up, transaction log growth, etc. As I noted above, growing the log file will impact performance.
28 Februari 2012 13:55
As previously stated, log file growth consumes resources and can block processes until complete. Also, depending how you initially sized the log file and its growth properties, you could have hundreds/thousands VLF's, which could certainly impact performance. If your database is mirrored, for example, the size/growth of the log file *may" also end up affecting performance depending on the mirroring configuration, pipe, etc.
SQL Server DBA
Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you
29 Februari 2012 5:17How to limit the log file size to keep track of one week? I want to truncate log file every week automatically.
29 Februari 2012 13:23Penjawab Pertanyaan
What recovery model is the database with the transaction log in question?
Truncating the transaction log doesn't make it physically smaller, it stays the same size. What happens is the active vlfs that can be reused are marked as inactive and can be overwritten and reused. When the log wraps back around it can then write over those vlfs instead of having to grow the file.
The following links should give you a plethora of explanation about log files and internals.
- Ditandai sebagai Jawaban oleh Iric WenModerator 07 Maret 2012 6:31