אוק10
Written by:
ronen ariely
10/10/2014 00:36 
Hello everyone,
Our case study
In this short blog I will post a simple Stored Procedure code, which you can use in order to find who was the last one who deleted a record from a table. This is mainly a basic work with the log file, and only as example. It is however, Very useful when your boss or you client, delete a row, and start to shout at you, why the data is not accurate, or why our application have a bug :-)
have you been there?
Our Solution
use tempdb
GO
-- Who deleted my record last time?!?
CREATE PROCEDURE Ariely_WhoDeletedRecordLasTime_sp
@TableName sysname
AS
Declare @WindowsusersecurityID varbinary(85)-- = 0x01050000000000051500000010272FD6C41215BDEC0EB034E9030000
SELECT @WindowsusersecurityID = [Transaction SID]
FROM fn_dblog(NULL, NULL)
WHERE
[Transaction ID] = (
SELECT top 1 [Transaction ID] FROM fn_dblog(NULL, NULL)
WHERE [Operation] = 'LOP_DELETE_ROWS' and [AllocUnitName] like '%' + @TableName + '%'
order by [Transaction ID] desc
)
AND [Operation] = 'LOP_BEGIN_XACT'
print @WindowsusersecurityID
SELECT
SUSER_SNAME(@WindowsusersecurityID) AS [Who deleted my record last time]
GO
create table T (ID int identity(1,1), Col nvarchar(100))
GO
insert T values ('a')
GO 100
select * from T
GO
delete T where ID = 3
GO
execute Ariely_WhoDeletedRecordLasTime_sp @TableName = 'T'
GO
DROP TABLE T
DROP PROCEDURE Ariely_WhoDeletedRecordLasTime_sp
GO
Comments
 ImportantI |
|---|
Notice: Fn_dblog is undocumented command and has some performance implications so please dont use it on production or if urgent use at own risk. |
| ImportantI |
|---|
Notice: Deleted records can be read from transaction log unless all Virtual log files which were part of that transaction has been underwritten by new information, and as long as they are still there, and VLF was not reused (we can read records after CHECKPOINT using the flag 2537). |
Thanks
* thanks to Shashank Singh, for the suggestions to add comments about undocumented and restrictions.