Post reply

Can I run a Trace on a DB as it's being restored?

  • April 9, 2008 at 10:52 am

    #154270

    I'm having intermittent problems with a full transaction log on a given DB. It's a third party app and the vendor (as well as past experience) indicates that the log file size and backup frequency are both reasonable. Due to the timing of the event we think this is user/application driven, but it never happens when I'm actually running a trace.

    So I'm wondering if I could run a Profiler Trace on a database as I'm restoring backed up transaction logs and thus see what happened to fill up the log file.

    For example: transaction log backups happen every four hours, and are typically no more than a couple MB. But 45 minutes after the last tlog backup, the file is full so I run another backup and it's 79MB, but the log file itself is appropriately truncated. If I restore the database on a test server, can I start a profiler trace just prior to restoring the last big transaction log backup and then watch the transactions as they're replayed by the restore?

    Is this worth trying, or am I misunderstanding how tlog restores work?

  • April 9, 2008 at 1:33 pm

    #800602

    Profiler won't show you anything. Profiler captures events sent to the server. When a tran log is restored, the SQL engine works through the tran log and just does the required modifications. It doesn't replay the commands.

    You may want to take a look at a 3rd party log explorer. There are a few around. I've never used any, so can't give you any recommendations. I'm sure someone else will be able to recommend one.

    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

  • April 9, 2008 at 3:10 pm

    #800655

    GilaMonster (4/9/2008)

    Profiler won't show you anything. ...

    You may want to take a look at a 3rd party log explorer. ....

    Thanks for the info; this will save me some time. Usually, I like to experiment, but this idea seemed a bit too clever for my own good, so I thought I'd ask for a reality check.

    I'm just trying to find an alternative to leaving a trace running for potentially days at a time, so the log explorer sounds like a good idea.

    Thanks again,

    MIJ

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

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