time to write a transaction log

• sumitagarwal.hcst

  SSC Veteran

  Points: 217

  More actions

  November 4, 2013 at 4:51 pm

  #300738

  Hi All,

  I would like to know that what percentage of time , SQL server is spending to write the transaction log file.

  I mean ig one transaction gets committed in a min, so in that one minute how long SQL server took to write it first in T Log file. Please provide me some script or DMVs.

  PS: I am using SQL Server 2005

• Keith Tate

  SSCrazy Eights

  Points: 9938

  More actions

  November 4, 2013 at 6:17 pm

  #1664012

  If this is a controlled environment you could use the following script (change database name):

  SELECT file_id,

  io_stall_write_ms,

  io_stall,

  num_of_writes

  FROM sys.dm_io_virtual_file_stats(DB_ID('DATABASENAME'), 2);

  http://technet.microsoft.com/en-us/library/ms190326.aspx

  Run it before your transaction and then after it to collect the deltas.

  ---

  
  Microsoft Certified Master - SQL Server 2008
  Follow me on twitter: @keith_tate

  Forum Etiquette: How to post data/code on a forum to get the best help[/url]