Post
Ask Question

How To Read SQL T-Log File

This morning, one of my colleagues came to my desk with an interesting question:  “Is there any way to read SQL T-Log file?” I didn't know. But later, I searched on Google and found one of the several un-documented functions used to read T-Log file.

Using this function, we can get the list of all transactions performed in the database. Function name is fn_dblog() (Formally known as DBCC command).

The fn_dblog() will accept two parameters,

  1. Starting log sequence number (LSN). We can specify null, it will return everything from start of log.
  2. Ending log sequence number (LSN). We can specify null, it will return everything to end of the log.

    1. Create Database SampleDatabase  
    2. Go  
    3. use SampleDatabase  
    4. GO  
    5. Create Table Inventory   
    6. (  
    7.     ID Int identity (1,1),  
    8.     ProductName varchar(100),  
    9.     Quantity int  
    10. )  
    11. Insert into Inventory(ProductName,Quantity) values ('Soap',10),('Tooth Paste',20)  

Demo

I have created a new database named “SampleDatabase”. Then, I created a new table called “Inventory” and inserted some values in it.

  1. SELECT [Current LSN]  
  2.       ,[Operation]  
  3.       ,[Transaction ID]  
  4.       ,AllocUnitName   
  5.       ,[Transaction Name]  
  6.       ,[Transaction SID]    
  7.       , SUSER_SNAME([Transaction SID]) AS DBUserName  
  8.       ,[Begin Time]    
  9.       ,[Lock Information]  
  10. FROM fn_dblog(NULL, NULL)  
  11. WHERE SUSER_SNAME([Transaction SID]) = 'Nisarg-PC/Nisarg'  
  12. AND   [Transaction Name] in ('CREATE TABLE','Insert','Delete')  
Now, I want to get all the transactions (Insert, Update, Delete, create Table) performed on the database so I can run the below query.

In the above 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 Nisarg-PC\Nisarg.

This function is undocumented and you should use it with caution.