Today morning, one of my colleague came to my desk with an interesting question. “Is there any way to read SQL T-Log file?” I did not have idea about it I searched on google and I found an un-documented functions which is used to read T-Log file. Using this function, we can get list of all transaction performed on database. Function name is fn_dblog() (Formally known as DBCC command).
The fn_dblog() will accept two parameters:
- Starting log sequence number (LSN). We can specify null, it will return everything from start of log.
- Ending log sequence number (LSN). We can specify null, it will return everything to end of the log.
/*Create Sample Database*/
Create Database SampleDatabase
/*Create table and add some values in sample database*/
Create Table Inventory
ID Int identity (1,1),
Insert into Inventory(ProductName,Quantity) values ('Soap',10),('Tooth Paste',20)
I have created new database named “SampleDatabase”. Then I created new table called “Inventory” and insert some values in table.
Now I want to get all the transaction (Insert, Update, Delete, create Table) performed by on database. So, I can run below query.
SELECT [Current LSN]
, SUSER_SNAME([Transaction SID]) AS DBUserName
FROM fn_dblog(NULL, NULL)
WHERE SUSER_SNAME([Transaction SID]) = 'SQL Login / user'
AND [Transaction Name] in ('CREATE TABLE','Insert','Update','Delete')
In 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.