Post reply

Transaction Log Backup file inserted into table 2008R2

  • June 9, 2014 at 9:22 am

    #286404

    Is it possible to read a transaction log backup file into a table to run queries on the dataz? (Not current translog)

    Our transaction log grew overnight and I'm interested to know exactly what transactions were happening at the time.

    The log is generously sized and backed up every 15 mins and has not come close to filling up in at least 6 months. I'm interested in finding out why it suddenly became large enough to grow. Any help? 🙂

    Thanks, D

  • June 9, 2014 at 9:42 am

    #1720144

    While it's possible to query a log backup, the results are not easy to interpret. There are no queries listed in the log, just the records of what objects were changes and what the data changes were.

    Before you go the log route, look in the default trace, see if there were any DDL changes which may have cause large amounts of logging, look in the error log, look in the job history.

    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 9, 2014 at 12:08 pm

    #1720175

    Thanks for your reply!

    I've looked at the error log/job history and there are no entries for +-5 mins around the time of the grow. Also, everything that should have run overnight did so without error and within reasonable time limits.

    As for the default trace, the only thing notable is that the log was backed up at 00:45:00.703 and grew at 00:45:21.143. :crazy:

    Would you have any more recommendations or ref links for viewing backed up transaction logs?

    Thanks, D

  • June 9, 2014 at 2:31 pm

    #1720214

    Bear in mind that it could be something as simple as an open transaction. That could require reading through multiple log backups.

    How many days do you have spare to do this analysis? Bear in mind that it's undocumented and not easy to track even if you are familiar with the log layout.

    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

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

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