Post reply

Querying the transaction log

  • June 4, 2014 at 1:39 am

    #308760

    Hi Guys,

    Just wondering if there was a way of querying the transaction log to ascertain particularly large queries filling it?

    If you have taken over another persons solution and the transaction log seems to be filling very fast and want to break it down to find out what are the main causes, what would the best way be?

    Cheers,

  • June 4, 2014 at 4:05 am

    #1718768

    If you feel that Transaction log is filling quickly and would like to find out the issue...

    1. You can start SQL Server trace for the specific database to find out exact activities going on on the server.

    2. Another way is to find out the opentransaction which is long running and filling the logfile. Once you have SPID you can find the command which is executed by specific user by using INPUTBUFFER.

    HTH

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • June 4, 2014 at 6:52 am

    #1718807

    Server-side trace or extended events to trace what's running.

    While it is possible to query the transaction log, all you will be able to see is the details of what operations happened, not what queries were run

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 4, 2014 at 8:17 am

    #1718847

    ApexSQL has a product that allows you to see tlog activity in detail and even create replay/undo scripts from it. Very slick stuff that has helped a number of my client's out after "oopsie" moments.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • June 5, 2014 at 1:46 am

    #1719102

    Thanks, was more so looking to try and break down the tran log after a weeks loading and querying but it seems that it isn't that straight forward.

  • June 5, 2014 at 3:54 am

    #1719134

    devorlask (6/5/2014)

    Thanks, was more so looking to try and break down the tran log after a weeks loading and querying but it seems that it isn't that straight forward.

    No, it isn't.

    By querying the log you'd be able to see what tables were modified when, but you wouldn't be able to get the query that did the modifications to tie it back to a procedure/job/etc

    Also, if you have either regular log backups or a DB in simple recovery, there won't be a week's worth of log records in the transaction log. Just since the last log backup/checkpoint

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 5, 2014 at 8:49 am

    #1719266

    devorlask (6/5/2014)

    Thanks, was more so looking to try and break down the tran log after a weeks loading and querying but it seems that it isn't that straight forward.

    It really is easy. Try a demo of ApexSQL's product(s) and see if it doesn't do what you want - even out of tlog backup files.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 7 posts - 1 through 6 (of 6 total)

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