Menu
header photo

SQL-Talk

Talks all about SQL Server and Azure

Transaction Log File Demo

--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 ('OPTIMIZE\REHAN');
GO
 
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
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