How to get last truncated time for each tables in SQL Server database

I have a SQL Server 2012 database. Is it possible to get last truncated time for all tables in the database?

Note: Recovery mode is SIMPLE in my scenario. Is it possible if I change it to FULL ?

Remember that truncate is an efficiently logged operation.

Since you are running in Simple Recovery mode and provided your T-log is not truncated, you can get it using undocumented fn_dblog:

SELECT [Current LSN]
    ,[Operation]
    ,[Begin Time]
    ,[Transaction ID]
    ,[Transaction Name]
    ,[Transaction SID]
    ,SUSER_SNAME([Transaction SID]) as UserWhoIssuedTruncate
FROM fn_dblog(NULL, NULL)
WHERE Operation = 'LOP_BEGIN_XACT' and
    [Transaction ID] IN (
        SELECT [Transaction ID]
        FROM fn_dblog(NULL, NULL)
        WHERE [Transaction Name] = 'TRUNCATE TABLE'
        )
GO

Click here to enlarge

Alternatively, you can run a server side trace or turn on SQL Audit.

Always give minimum permissions (principle of least privilege) to your users.


Is it possible if I change it to FULL ?

Yes it is (and recovery model should be defined by your business - do you want to have point in time restore? How much data your business can afford to lose? etc.) You have to start maintaining your T-log by running regular log backups. This will keep your T-log at a sensible size.

Edited
Query uses "IN" instead of "=" as multiple results in the sub-query are possible.

StackExchange Database Administrators Q#148546, answer score: 11