Transactions names meaning In Transaction Logs

Question

Post reply

Transactions names meaning In Transaction Logs

IT researcher

SSCertifiable

Points: 7569
More actions
May 25, 2022 at 6:14 am

#4038816
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?
May 27, 2022 at 10:19 am

This was removed by the editor as SPAM
May 31, 2022 at 11:52 am

This was removed by the editor as SPAM
May 31, 2022 at 12:57 pm

This was removed by the editor as SPAM

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

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

Site Owners SSC Guru Points: 80320 More actions · Answer 1

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

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

Mr. Brian Gale SSC-Insane Points: 24692 More actions · Answer 2

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.

ScottPletcher SSC Guru Points: 100949 More actions · Answer 3

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) A socialist is someone who will give you the shirt off *someone else's* back.