SQL Server 2017 sys.fn_full_dblog

There is a new DMF in SQL Server 2017 called sys.fn_full_dblog which is a more powerful alternative for the current sys.fn_dblog.
 
As shown below, sys.fn_full_dblog has several more parameters, but the returned columns are still the same.

--current sys.fn_dblog
SELECT TOP 10 * FROM sys.fn_dblog(null,null)
--SQL server 2017
SELECT TOP 10 * FROM sys.fn_full_dblog(null,null,DB_ID(),null,null,null,null,null)

image
The parameters available for sys.fn_dblog are:
image2
The parameters available for the new sys.fn_full_dblog are:
image3
With these extra parameters you can now target multiple databases etc.

SELECT a.[name] AS [database_name], COUNT(1) AS records
FROM sys.databases a
CROSS APPLY sys.fn_full_dblog(null,null,a.database_id,null,null,null,null,null) b
GROUP BY a.[name];

image4
NB: Both sys.fn_dblog and sys.fn_full_dblog are currently undocumented.

Leave a Reply