When was data last inserted/updated/deleted in a table?

Question

I have found this solution, but this information is lost everytime the SQL Server service is restarted, or in other words, it is not persistent.

Is there another way I can find out when a table was last updated even if the server has been shut down after the fact?

Answer 1

If the data in the other solution is acceptable to you, then you could simply roll your own data collection to make it persist. For example, create a table to store object_id and last seek/scan/update. Every n minutes, take a snapshot of the DMV. Then check the max in the snapshot, and if it's greater than the max in the rollup, update the rollup. You can keep the snapshots as history or you can clean them up over time once you've coalesced.

Answer 2

If you have access to SQL Server Enterprise edition, check out Change Tracking. That's a pretty complex solution, however. Ultimately it may just be easier to add an LastUpdated column, as already suggested.

Answer 3

I would also point out that you are going to get better milage by either creating a log table (which keeps track of inserts/deletions/updates) or adding flags with dates to your records (which would cost some additional disk space per record).

Either way SQL Server does not keep track of this by default as it would get expensive for high transaction DB's

Answer 4

This may be helpful to you:

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, 
    last_user_update, *
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'Database Name');