When was my SQL Server Database last accessed?

Few days back, one of my friends asked me whether there is a way to find the last accessed date of their SQL Server databases. What he really wanted was, finding out databases that are not being used. Nothing came into my mind immediately. I tried to remember a catalog view, a dynamic management function for this but nothing popped up.

Few things came to my mind later. First thing was SQL Server Auditing. I thought that there will be an AUDIT GROUP for database login. Bad luck, it is only available at server level only. I found out few posts related to this who have used DATABASE-LEVEL groups to track statements like SELECT, and most of the posts are for seeing the last access for tables, not for the database. Again, I thought it is bit difficult to implement, because my friend manages more than 60 databases :). So, I gave up the idea of using SQL Server Auditing for finding out the last accessed time for databases. If anyone has used auditing for seeing the last accessed date for databases, please let me know the way. Anyway, I ended up with this, though it has a problem too.

SELECT DatabaseName, MAX(LastAccessDate) LastAccessDate

FROM

    (SELECT

        DB_NAME(database_id) DatabaseName

        , last_user_seek

        , last_user_scan

        , last_user_lookup

        , last_user_update

    FROM sys.dm_db_index_usage_stats) AS PivotTable

UNPIVOT 

    (LastAccessDate FOR last_user_access IN

        (last_user_seek

        , last_user_scan

        , last_user_lookup

        , last_user_update)

    ) AS UnpivotTable

GROUP BY DatabaseName

HAVING DatabaseName NOT IN ('master', 'tempdb', 'model', 'msdb')

ORDER BY 2

What I have used here is sys.dm_db_index_usage_stats. This goes through all indexes (including heap) and shows last accessed time for them. By looking at these accessed time, we can determine the last accessed time for the databases, and can see whether the databases are being used or not. Here what I see when I run the script:

Drawbacks
Biggest problems with this technique is, if the SQL Server service is restarted, everything is reset. So, this is not applicable for servers which get restarted frequently. Other problem is related to Full-Text-Catalogs. If you have enabled Full-Text-Index on your tables, you might see a record saying that database has been accessed without any user operations. This is because of Full-Text-Indexes.