time to write a transaction log

  • sumitagarwal.hcst

    SSC Enthusiast

    Points: 107

    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: 9743

    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]

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply