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
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
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
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.
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:
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