In the last Tour of the Transaction Log we looked at how INSERT operations behave in the transaction log.
In this part we will be looking at DELETE statements and how we can find out who deleted that record in your table!

Again, commands in this article are undocumented by Microsoft and should always be used with caution!

Setting up the test data

Just like last time we will be creating a simple test database, if you followed the previous tour you can skip these steps:

CREATE DATABASE [TlogTest] ON  PRIMARY

( 
NAME = N'TlogTest', FILENAME = N'E:\Data\TlogTest.mdf' , SIZE = 3072KB ,
FILEGROWTH = 1024KB 
)

LOG ON

( 
NAME = N'TlogTest_log', FILENAME = N'E:\Log\TlogTest_log.ldf' , SIZE = 1024KB ,
FILEGROWTH = 10%
)

GO

Let’s create a table:

USE [TlogTest]

CREATE TABLE [Table01]

(
[n1] INT IDENTITY,
[n2] CHAR DEFAULT 'a'
);

And insert some data:

INSERT INTO Table01 DEFAULT VALUES;
GO 1000

Now that our test data is in let’s move forward to the fun part!

Follow the DELETE

In my previous article I discussed how you can read the transaction log using the fn_dblog function. We will be using that function again to trace the DELETE statement.

Let’s delete a record using the test database we created:

USE [TlogTest]

DELETE FROM Table01
WHERE n1 = 1;
GO

We’ll use almost the same query as in the previous article only this time we will be looking for DELETE OR LOP_DELETE_ROWS operations and we added the ‘Begin Time’ column so we can see at what time the delete was performed:

USE [TlogTest]

SELECT

  [Begin Time],
  [Current LSN],
  [Operation],
  [Transaction Name],
  [Transaction SID],
  [Page ID],
  [Slot ID]
FROM fn_dblog(DEFAULT, DEFAULT)
WHERE [Operation] = 'LOP_DELETE_ROWS' 
OR [Description] LIKE '%DELETE%'
ORDER BY [Current LSN] DESC;

I got the following results back from the query above, keep in mind your ID’s will probably be different then mine!

The LOP_BEGIN_XACT operation is the actual SQL DELETE command, the LOP_DELETE_ROWS the transaction that performed the actual delete. By taking a look at the Begin Time of the LOP_BEGIN_XACT event we can find out the date and time the delete was performed.

Who performed that delete?

The next step is finding out who actually performed the DELETE of the record.

Copy the Transaction SID you received when you executed the query in the previous step, in this example it’s ‘0x0105000000000005150000006C453472DCC60931F433E811F4010000’

Paste the Transaction SID in the following query:

SELECT SUSER_SNAME(insert Transaction SID
here);

Now execute the query!

As you can see the Windows user Administrator performed the delete in my case!

In our next Tour of the Transaction Log we will digging a little deeper, as we are going to try and find out who deleted a record after the transaction log has been flushed!