Lets say I have a SQL Server with 100 databases on it. How can I find out which ones are actually being used? (without turning them all off and waiting for the complaints to come in) So 'have been accessed in the last week' or something like that.
I've tried the data file dates but they don't seem to represent that and databases do not seem to have a property that reflects this either.
You might be able to get this information by using some of the system views related to performance.
Look at sys.dm_db_index_usage_stats. The columns last_user_seek/last_user_scan/last_user_lookup/last_user_update represent the last time the respective index (heap or b-treee) was used. These values reset after server restart, so you must check them after the server was up an running for a sufficient time.