October 12, 2017 at 10:31 am
I have been researching this waittype quite a bit today. The one question that I do have is, if we see excessive writelog wait types during a time frame, should I expect that the next transaction log backup to be larger in size?
Thank you
October 12, 2017 at 10:59 am
GBeezy - Thursday, October 12, 2017 10:31 AMI have been researching this waittype quite a bit today. The one question that I do have is, if we see excessive writelog wait types during a time frame, should I expect that the next transaction log backup to be larger in size?Thank you
You could but not necessarily would you always see this (sounded better than it depends..). There are different scenarios when things are written to disk.
Not sure what you have found in your research but here is an interesting article on explicit vs implicit transactions and the effects on log IO :
What is WRITELOG waittype and how to troubleshoot and fix this wait in SQL Server
Sue
October 12, 2017 at 11:02 am
The 2 are not necessarily correlated. You can get a quick summary of file latency and a view of how much log space is used like this:SELECT DB_NAME() AS database_name, f.name AS file_name, f.physical_name, f.size/128 AS size_MB, FILEPROPERTY(f.name, 'SpaceUsed')/128 AS used_MB,
vfs.io_stall_read_ms / NULLIF(vfs.num_of_reads, 0) AS avg_read_latency_ms,
vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0) AS avg_write_latency_ms,
vfs.io_stall / NULLIF(vfs.num_of_reads + vfs.num_of_writes, 0) AS avg_total_latency_ms
FROM sys.dm_io_virtual_file_stats(DB_ID(), 2) AS vfs
INNER JOIN sys.database_files f ON f.file_id = 2
there are a number of different factors that could cause log write waits.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply