4

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?

Community
  • 1
  • 1
Brandon Moore
  • 8,590
  • 15
  • 65
  • 120
  • Can you make accessing the data only allowed through a stored procedure? – mellamokb Apr 20 '12 at 22:49
  • @mellamokb There are tons of processes going on, many that I don't even know about yet, that would break if we did that. From a DBA's perpective SPs are great, but from this developer's perspective they 'usually' don't provide significant benefits to outweigh their downsides. Jeff Atwood actually has a great article about that on Coding Horror that I completely agree with: http://www.codinghorror.com/blog/2004/10/who-needs-stored-procedures-anyways.html – Brandon Moore Apr 21 '12 at 17:42

1 Answers1

12

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.

Russell Hart
  • 1,842
  • 13
  • 20