1

In my previous question, I asked about how to know about what happened in my SQL-Server 2019 database. After a while, I found out that there is some kind of table, called fn_dblog, which gives some interesting information, but I still have some questions (literally copied from that previous question):

SELECT Top 10 [Begin Time] [End Time]
  FROM fn_dblog(null,null)
  WHERE UPPER(AllocUnitName) LIKE '%TABLENAME_STATUS%'

... where [Begin Time] and [End Time] are proposed by my Microsoft SQL Server Management Studio. To my surprise, the results look like:

Title: End Time
NULL
...
NULL

Now I have the following questions:

  • What happened with the [Begin Time] column?
  • Why is [End Time] not filled in?
  • When I copy a normal SQL query result into clipboard, the column names are copied too, but when I copy the results of an SQL query concerning fn_dblog, the column names are not present in the clipboard. Why is that and how can I change that?

Next to that, I also found another "table", called fn_full_dblog, but that's even worse:

SELECT Top 10 * 
  FROM sys.fn_full_dblog(null,null, null, null, null, null, 
                         null, null, null, null,null)
  WHERE UPPER(AllocUnitName) LIKE '%TABLENAME_STATUS%'

=> no result at all!

Can somebody give me some information on how to read fn_dblog and possibly fn_full_dblog or any other "table", which might use interesting information?

Thanks in advance

1
  • 1
    the column is only populated when relevant to the log entry. a quick check shows that only LOP_BEGIN_XACT has begin time and LOP_COMMIT_XACT has end time. transaction ID will show you all the records in the transactions log, which you can use to fine start and end times of a specific transaction.
    – Bob Klimes
    Jul 11, 2022 at 14:26

1 Answer 1

4

The most important thing is that fn_dblog is undocumented.

There is no official support from Microsoft; it can be changed or removed at any point, and you shouldn't rely on it.

Since it's undocumented, you can't get an official answer to the questions about the columns.

Secondly, it's not the right tool for the job. You are reading from the transaction log, which is cyclic. You would have to periodically poll it to ensure you're not missing a data entry.

Also, reading and filtering the transaction log is not practical. There are no indexes, so you read the (potentially large) log every time.

That said, here's an article from the function's author.

https://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/

2
  • So the moral of story is: Microsoft, for once, comes with something which looks very interesting ... and then they make it unreliable. They'll never learn it :-p
    – Dominique
    Jul 11, 2022 at 14:16
  • @Dominique To be fair, it's a pretty roundabout way to solve your root problem that's mentioned in your other question. There's much more standard (and arguably simpler) ways to solve your problem as the answers in your other question mention, which Microsoft has already provided. +1 to Microsoft for the multiple options they've already provided the end user. 😉
    – J.D.
    Jul 11, 2022 at 16:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.