en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP

 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize

Recent Entries

Minimize
אוק10

Written by: ronen ariely
10/10/2014 00:36 RssIcon

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.