Breaking down transaction log usage

  • Hi

    A server of mine is running multiple jobs fairly continuosly all of which are hitting the transaction log.

    Infrequently and in no pattern I can recognise the log blows up rapidly and I can guess whats causing it from sp_who2

    Im trying to script a way that can identify and display by spid the % contribution to filling up the log

    i.e.

    spid

    1 20%

    2 80%

    Is this possible?

  • Not without a third party log reading tool.

    Why not run a server side trace and capture the transactions being run. Then you can figure out the bad actor.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Check BOL for sys.dm_db_task_space_usage. It displays tempdb usage by spid (session_id).

    Once you get the session_id of some of the worst offenders, you can join up to sys.dm_exec_sessions, sys.dm_exec_requests, and/or sys.dm_exec_connections to determine specific procs/calls/users/whatever.

    Note that the amount of space allocated does not necessarily equate to log space used. For example, if a spid had allocated a temp table, and then repeatedly updated it without adding more rows, it would cosume significantly more log than the file allocation. Even so, data returned by the above DMV will highlight most of the tempdb-log-consuming spids.

    -Eddie

    Eddie Wuerch
    MCM: SQL

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

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