7

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 ?

12

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

enter image description here

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.

4

I'm afraid it's not possible to know the last time a table was truncated without setting up something to capture the event upfront.

The default trace does not hold information for truncate events and sys.dm_dm_index_usage_stats won't help you here either.

Your options are:

  1. A trace
  2. An Extended Events session
  3. An audit (I'm not 100% sure it can capture TRUNCATE events)

As far as your second question is concerned, the recovery model for production databases is usually FULL, because restoring the database at a specific point in time is generally speaking a requirement. Databases that contain only data that can be easily and quickly rebuilt from other data sources usually don't need to be restored at a specific point in time, so they often are in the SIMPLE recovery model

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.