Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
8  
If this is very important you may need to add some triggers. SQL Server doesn't keep this info by default because it can get so expensive. –  JNK Mar 6 '12 at 19:04
    
Agreed w JNK. You'll want some kind of last updated tracking that is actually logged to table(s). –  Eric Higgins Mar 6 '12 at 19:56
1  
@JNK I'm investigating some kind of auditing mechanism like the one you're suggesting, but for this particular case I'd need retroactive data. –  ivanmp Mar 6 '12 at 21:55
2  
@ivanmp can't get retro unfortunately. If SQL Server stored this data, then you would need a large % of your data size in audit tables to be maintained. –  JNK Mar 6 '12 at 21:57

4 Answers 4

up vote 4 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

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

share|improve this answer

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');
share|improve this answer
2  
Have you read the comments to the question and the answers? At least you should address the points brought up there (e. g. SQL Server doesn't keep this info by default because it can get so expensive.) –  dezso Sep 9 '13 at 8:12
1  
Also in the very question you're answering: this information is lost everytime the SQL Server service is restarted - how does this answer address that problem? –  Aaron Bertrand Sep 11 '13 at 13:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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