Reading Transaction Log

Someone or a service deleted a record from a table. We will need to know when it was deleted and who deleted the record. I read every transaction log between a specific time to find out if there was a delete statement in transaction log.  Transaction logs are located on E:\

Reading Transaction Log

Option 1

SELECT [Current LSN], [Transaction ID], [Transaction Name], [Operation], [Begin Time], [PartitionID], [TRANSACTION SID],LEFT ([Description], 40) AS [Description]  FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'E:\TEST.trn',

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)

WHERE Operation = 'LOP_DELETE_ROWS'

GO

Option 2

In the script below, we passed table name and operation

WITH CTE

as

       (SELECT [Transaction ID], count(*) as DeletedRows

       FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'E:\TEST.trn',

       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

       DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)

       WHERE Operation = ('LOP_DELETE_ROWS')

       AND [PartitionId] = (SELECT sp.partition_id

                            FROM sys.objects so

                            INNER JOIN sys.partitions sp on so.object_id = sp.object_id

                            WHERE name = 'TESTTABLE')

       GROUP BY [Transaction ID]

       )

SELECT [Current LSN], a.[Transaction ID], [Transaction Name], [Operation], [Begin Time], SUSER_SNAME([TRANSACTION SID]) as LoginName, DeletedRows

FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'E:\TEST.trn',

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) as a

INNER JOIN cte on a.[Transaction ID] = cte.[Transaction ID]

WHERE Operation = ('LOP_BEGIN_XACT')

Option 3

If we know specific transaction SID, we can find who ran the delete.

SELECT

    [Current LSN],

    [Operation],

    [Transaction ID],

    [Begin Time],

[End Time],

[Transaction Name],

LEFT ([Description], 40) AS [Description],

SUSER_SNAME ([Transaction SID]) AS [WhoDidIt?],

[PartitionID],

[Num Elements] ,

    [RowLog Contents 0],

    [RowLog Contents 1],

    [RowLog Contents 2],

    [RowLog Contents 3]

FROM

     fn_dump_dblog (NULL, NULL, N'DISK', 1, N'E:\TEST.trn',

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)

WHERE [Transaction ID] in('0000:0084ff66','0000:0084ff66','0000:0085002c')

GO

I used above three queries to find out who deleted a record and delete time.

Thank you for reading.

Create SQL Login in SQL AlwaysOn availability group servers

How to create SQL login in SQL AlwaysOn Availability group databases

First create SQL Login in Primary replica
Example : Login : TESTLOGIN, Password : test, Database : DBA, db_datareader

Find SID of SQL Login in Primary replica

Run the following query in primary SELECT SUSER_SID ('TESTLOGIN') to find SID of the SQL login in Primary replica
Copy the query result which is SID, Example : 0x4063F34B8AF7A041B66CF243AAC79ECC

Create the same SQL Login in Secondary node using the script below. Paste the SID from primary node in SID area, password in passsword.
In the script below, update the areas LOGIN, PASSWORD, SID, DEFAULT_DATABASE

USE [master]

GO

CREATE LOGIN [TESTLOGIN] WITH PASSWORD=N'test' 

,SID=0x4063F34B8AF7A041B66CF243AAC79ECC, DEFAULT_DATABASE=DBA,

DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

After updating the query, Run the query in Secondary node

We use the same SID for SQL login in order to work after failover happens.

Thank you for reading.