--Step1: creating database
create database TLogDemo
--Step2: View T.Log file contents
dbcc log(TLogDemo)
--Step3: To view complete details
dbcc log(TLogDemo,3)
select * from fn_dblog(null,null)
--Step4: Creating sample table
use TLogDemo                      
go
create table Employees(empid int,ename varchar(40),sal money)
--step5: Insert row
insert Employees values(1,'Rehan',4000),(2,'Ravi',4500)
update employees set sal=4540 where empid=1
--step
dbcc log(TLogDemo,3) --Check two inserts at the end
--step6: Filtering T.Log file for INSERT
select * from fn_dblog(null,null)
where [Transaction ID] IN(
select [Transaction ID] from fn_dblog(null,null)
where [Transaction Name] like '%insert%')
--step7: To check changes made on Employees table
select * from fn_dblog(null,null)
where AllocUnitName like '%Employees%'
--step8
--Finding a transaction in the log for a particular user
SELECT
    [Current LSN],
    [Operation],
    [Transaction ID],
    [Begin Time],
    LEFT ([Description], 40) AS [Description]
FROM
    fn_dblog (NULL, NULL)
WHERE
    [Transaction SID] = SUSER_SID ('SQL-TALK\REHAN');
GO
--Finding a transaction in the log for a particular user between given timings

SELECT
    [Current LSN],
    [Operation],
    [Transaction ID],
    [Begin Time],
    LEFT ([Description], 40) AS [Description]
FROM
    fn_dblog (NULL, NULL)
WHERE
    [Transaction SID] = SUSER_SID ('OPTIMIZE\REHAN')
AND ([Begin Time] > '2015/06/03 11:18:15' AND [Begin Time] < '2015/06/03 11:18:25');
GO

--Finding UPDATE transactions.

SELECT
    [Operation], [Transaction Name], [Transaction SID],
    SUSER_SNAME ([Transaction SID]) AS [WhoDidIt?]
FROM fn_dblog (NULL, NULL)
WHERE [Operation] = N'LOP_BEGIN_XACT'
AND [Transaction Name] = 'UPDATE';
GO