Post reply

Isolating which transaction is filling my log?

  • November 6, 2014 at 4:47 am

    #298207

    So my tran log has filled (avail disk space) a few times and I'd like to isolate what is filling it & decide if to amend the code or increase space.

    The recovery model is SIMPLE.

    By the time I get to work, there are no open transactions so I need to either investigate retrospectively or put some logging on for the next time.

    a) How do I go about investigating this?

    Many thanks

  • November 6, 2014 at 7:41 am

    #1757002

    You should record the activity in your server to a table you can query afterwards to investigate what was running at the time.

    If this was a SQL Server 2008 , I would have recommended using the Data Collector, but since you are posting in the SQL Server 2005 forums, I suppose you're on 2005.

    You could use Adam Machanic's fine procedure sp_WhoIsActive and output the data to a table (there's a parameter for that).

    Run it on a SQL Agent job every minute and you should be able to capture the statements that fill your log file.

    -- Gianluca Sartori

  • November 6, 2014 at 8:17 am

    #1757022

    Thanks for taking the time to reply.

    I'd prefer a retrospective solution rather than waiting for a re-occurence.

    I was wondering if there's a DMV somewhere that records transaction time ... or the number of records logged in the tran log per query. Kind of like sys.dm_exec_query_stats ... (but not). If you see what I mean.

  • November 6, 2014 at 8:43 am

    #1757036

    I'm afraid you'll have to monitor that when it's happening.

    There's no DMV that records what you want.

    -- Gianluca Sartori

  • November 6, 2014 at 8:51 am

    #1757038

    Ok, thank's for your time!

Viewing 5 posts - 1 through 4 (of 4 total)

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