Last user update for a table

Question

Since my server was started one month back, I am getting 2015-07-31 as the value of last_user_update for a table.

Does that mean my table’s data hasn't been modified (inserted, updated, deleted) since 2015-07-31?

Can I 100% believe the results returned by last_user_update in sys.dm_db_index_usage_stats?

I am using SQL Server 2012. I have checked through sys.dm_os_sys_info that my server was started last on 22nd April-2015.

I am only worried about modifications to the table after 31st July. The reason why I want to be sure is because my database has been used by another party after 31st July and the data is huge so I can't confirm now whether any updates have been done or not.

score 2 · Answer 1 · answered Aug 06 '15 at 11:39

Unless and until you try out some more observations and results of same, nothing can assure you 100 %. Although can assure you 99 % but rest you have to test.

Below query can give you what you said for dmv sys.dm_db_index_usage_stats as last modified date since restart on the usage of table.

SELECT last_user_update
FROM   sys.dm_db_index_usage_stats us
       JOIN sys.tables t
         ON t.object_id = us.object_id
WHERE  database_id = db_id()
       AND t.object_id = object_id('dbo.YourTable')

Also, below can give you an idea on same. Result might be same on last usage date, but depends what you are looking for. You need to understand the nature of that table as well depending upon the data out there.

SELECT
        [name]
       ,create_date
       ,modify_date
FROM
        sys.views
WHERE
        modify_date > DATEADD(day,-30,GETDATE())

In addition you will need trigger to observer what and when was it changed to analyse further.

Furthermore, if you have the default trace luckily out there , you might get some help that might get you close to what you are aiming for: Read Collecting the Information in the Default Trace

Last user update for a table

1 Answers1