Home Dashboard Directory Help
Search

Database last accessed time by Kimberly L. Tripp


Status: 

Active


70
1
Sign in
to vote
Type: Suggestion
ID: 659846
Opened: 4/12/2011 6:12:44 AM
Access Restriction: Public
0
Workaround(s)
view

Description

Would be great if sys.databases had a column that stored the last time someone connected/used a database. I wouldn't need this to be "up-to-the-minute" or guaranteed to be accurate. In fact, just the last DAY/DATE that it was accessed would be good enough to determine whether or not a database is being actively used.

Possibly even better, the date it was last used plus a fuzzy use_count.

Aaron Bertrand did a blog post (http://sqlblog.com/blogs/aaron_bertrand/archive/2008/05/06/when-was-my-database-table-last-accessed.aspx) in how to do this using audit or index usage stats but all of these have to be setup beforehand. What we want is a way to go to a server and determine how "stale" some of their databases are.

Thanks!
Kimberly
Details
Sign in to post a comment.
Posted by Microsoft on 12/2/2011 at 1:06 PM
We will consider this as a future feature for the database. At this time this is a low priority. However as part of auditing and compliance we are considering this for a future release.
Posted by Fabricio Briasco on 9/16/2011 at 12:09 AM
Raja, help us.. Please, it's very necessary..

thanks.
Posted by Microsoft on 8/1/2011 at 12:25 PM
Kimberly:

Thanks for the request for the feature. I am following with the dev team on:

1. Incorporating this as a feature
2. Mitigating it in the short term
3. Priorities compared to existing and planned work

Thanks for taking the time in bringing this to our attention.

regards,

Raja Krishnasamy
SQL Servetr Program Management.
Posted by DB007 on 4/12/2011 at 8:48 AM
Due to automated procedures connecting to systems for backup/indexing etc, it may be better to have a table somewhere which stores when individual users last connected to a particular database?
[e.g. new table: sys.access - linked between sys.databases / sys.users or even place into the sysusers table a new column so that we can track when a user last accessed which databases. sysdatabases may be too limiting]

This would remove all doubt and ambiguity about how stale a database was getting. Then we can make the executive decision to determine if user (and who) are still accessing the database and when was the last-access time to the database.
Posted by Glenn A. Berry on 4/12/2011 at 8:12 AM
This would be a very handy addition, and something I would be interested in collecting with my instance diagnostic quieries.
Sign in to post a workaround.