Have you ever wanted to be able to see the actual transactions that are contained in the transaction log file? Well there is a way to read the transaction log file using an undocumented function. In this article, I will show you how to browse the transaction log using this undocumented function.
To read the transaction log you I will be exploring using the undocumented function named “fn_dblog”. This function requires two parameters to be passed. The first parameter is the starting LSN (Log sequence number) and the second parameter is the ending LSN. If you wanted to read all the data in the transaction log then you will need to pass null values for both of these parameters like so:
SELECT * FROM sys.fn_dblog(NULL,NULL)
Let’s see how we can use this function by looking at an example. For this demonstration, I will be running the following code to place some records in the transaction log for the database in this demo:
CREATE DATABASE TranlogRead; GO USE TranlogRead; GO CREATE TABLE dbo.Color (Color varchar(10)); GO INSERT INTO dbo.Color VALUES ('Red'),('Yellow'),('Green') GO DELETE FROM dbo.Color WHERE Color = 'Red'; GO
In this code I first created a database named TranLogRead. I did this just to create an isolated testing database, so I could explore the transaction log for this isolated database. Next I created a table named “Color”. Once created I then inserted three rows, and then finished up by deleting the row that contained the color “Red”.
In order to find the CREATE TABLE, INSERT and DELETE transaction in the transaction log I can run the following code:
SELECT [Current LSN] ,[Operation] ,[Transaction ID] ,AllocUnitName ,[Transaction Name] ,[Transaction SID] , SUSER_SNAME([Transaction SID]) AS DBUserName ,[Begin Time] ,[Lock Information] FROM fn_dblog(NULL, NULL) WHERE SUSER_SNAME([Transaction SID]) = 'Greg-PCGREG' AND [Transaction Name] in ('CREATE TABLE','INSERT','DELETE')
In this code, you can see I used the fn_dblog function in the “FROM” clause. I also used the “WHERE” predicate to return only transaction log rows that involved a CREATE TABLE, INSERT and/or DELETE transaction created by database user Greg-PCGREG.
Keep in mind the fn_dblog function is undocumented and you should use it with caution. To find out more about the information returned by fn_dblog I suggest you remove the “WHERE” predicates from the above code and look at all the different transaction log information that this function returns. Additionally, you should explore using the “Transaction ID” column in a WHERE predicate to see all the different transaction log records that go along with a single transaction.
