This question is basically an extension of this question:
How can I tell if a database table is being accessed anymore? Want something like a "SELECT trigger"
In the query the author provided as a solution in his post, I assume a null value would mean that a table hasn't been accessed since the last time SQL Server was restarted.
My question is: Is there a way to tell when a table was last accessed, if the last access was before the last time SQL restarted? Also, how can I tell when the last time SQL was restarted?
For sql server start time in SQL Server 2008,
select sqlserver_start_time from sys.dm_os_sys_info
For last user access since server restart,
select DB_NAME(us.[database_id]) as [db], OBJECT_NAME(us.[object_id], us.[database_id]) as [object],
MAX(us.[last_user_lookup]) as [last_user_lookup], MAX(us.[last_user_scan]) as [last_user_scan], MAX(us.[last_user_seek]) as [last_user_seek]
from sys.dm_db_index_usage_stats as us
where us.[database_id] = DB_ID() AND us.[object_id] = OBJECT_ID('tblname')
group by us.[database_id], us.[object_id];
I log this table daily so I have it after restarts. It can also act as an index audit after they have been dropped.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With