On our company's SQL server, there are a bunch of databases that don't appear to be used. Is there a way to determine the last time someone used a particular db, connected to it, or ran a query against it?
+3
A:
I don't think there's a per-database statistic for last used date.
What you can do is attach SQL Server profiler to the database, with a filter on database name. You can leave this running for a few weeks and see if there's any activity.
Another option is to check Database Properties -> Reports -> Standard Reports -> Index Usage Statistics. If the last use of any index is very old, that's a good indication the database is not being used.
Alternatively, have a look at SQL Server Auditing. I haven't used it myself, but it looks like it might suit your needs.
Andomar
2010-02-03 17:26:00
+1 - worth a look at Aaron Bertrand's blog post on this very topic: http://sqlblog.com/blogs/aaron_bertrand/archive/2008/05/06/when-was-my-database-table-last-accessed.aspx
AdaTheDev
2010-02-03 17:39:24
You should put the link to Aaron's post as an answer - I'd say that was best that the poster will get.
Andrew
2010-02-03 17:42:16