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,
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 Database SampleDatabase
Create Table Inventory
ID Int identity (1,1),
Insert into Inventory(ProductName,Quantity) values ('Soap',10),('Tooth Paste',20)
I have created a new database named “SampleDatabase”. Then, I created a new table called “Inventory” and inserted some values in it.
SELECT [Current LSN]
, SUSER_SNAME([Transaction SID]) AS DBUserName
FROM fn_dblog(NULL, NULL)
WHERE SUSER_SNAME([Transaction SID]) = 'Nisarg-PC/Nisarg'
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.
HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.