3

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.

  • 1
    @muneshkumar DMV data gets flushed out / reset when SQL Server restarts. Unless you have some kind of server side trace running or your table explicitly tracks using something like LAST_CHANGED_DATE, it would be hard to find out the last user update to a table. – Kin Shah Aug 6 '15 at 13:33
  • What if they update a column that isn't indexed? Interesting problem. Im going to test this tomorrow. – Sir Swears-a-lot Aug 11 '15 at 9:07
1

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

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.