Transactions names meaning In Transaction Logs

  • I am using .LDF file to get the information about when was DB last modified.  But when I check the .Ldf file under "transaction name" column  following transaction names present.

    What is the meaning of these Transactions:

    Backup:CommitDifferentialBase
    Backup:InvalidateDiffMaps
    StartupDB
    SplitPage
    FirstPage Alloc
    PostOfflineRestoreFixups
    INVOKE
    RemapSysfiles1

    If above transactions are present should I consider as DB modified or not?

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • This was removed by the editor as SPAM

  • Possibly a dumb question but what problem are you trying to solve?  I am just trying to figure out what use case there would be to seeing when any change was done to a database.  Inserting a row (as an example), I can see being useful to know when it happened, but I care about that at the table level, not the database level.  I can't think of a use case where I would want to know the date/time that a database was modified.

    Now, more to your point, some of those are "it depends".  The next bit is just my understanding of things.  I could be mistaken and would not be surprised if I have this bit wrong, but I think it is accurate.  A split page is a good example of "it depends".  This can happen when a row is updated or inserted, so it is LIKELY caused by data changing.  Now I say "LIKELY" because this could ALSO occur if the index is reorganized.  Here, the data itself is not changing, but the location it is on disk is, so technically the database is different (would generate a different hash), but the data is the same.  First Page Allocation is similar I believe.  An empty table would not have that, but as soon as the first row goes in, the first page would be allocated.  BUT the same thing would occur if a new index is created.  I am not 100% certain on the other ones though.  I would expect that StartupDB wouldn't really be modifying anything, but it may be changing some system tables on the database to keep track of the last startup time (for example).

    But again, I am curious what problem you are trying to solve or what you are trying to determine by doing this...

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • SQL Server has a system view, sys.dm_db_file_space_usage.  In that view is column modified_extent_page_count, which tells how many db pages have been modified since the last full db backup.  If you capture that info periodically, you can tell how many pages in the db have changed, if any, during that period of time.

     

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

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

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