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?
-- Who deleted my record last time?!?
-- = 0x01050000000000051500000010272FD6C41215BDEC0EB034E9030000
@WindowsusersecurityID = [
ID] = (
+ @TableName +
[Who deleted my record
identity(1,1), Col nvarchar(100))
ID = 3
Ariely_WhoDeletedRecordLasTime_sp @TableName =
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.
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 to Shashank Singh, for the suggestions to add comments about undocumented and restrictions.