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

Dominique
  • 609
  • 1
  • 7
  • 23

1 Answers1

5

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/

Zikato
  • 5,619
  • 1
  • 17
  • 34