About Me

Barking, Essex, United Kingdom
MCITP DBA ,MCITP BI & Oracle OCP 11G

Monday, June 23, 2014

Who truncated the table and what time


 SET NOCOUNT ON;
  DECLARE @ObjectName SYSNAME
  DECLARE @TransactionID NVARCHAR(500)

  SET @ObjectName = 'dbo.tablename'
 
  SELECT @ObjectName AS ObjectName
      ,   [Transaction Name]
      ,   SUSER_SNAME([Transaction SID]) AS UserName
      ,   [Begin Time]
      ,   Operation
      ,   [Transaction ID]
      FROM fn_dblog(NULL, NULL)
        where   [Transaction Name] LIKE 'TRUNCATE%'

          AND Operation = 'LOP_BEGIN_XACT'

No comments:

Post a Comment