SQL Script: - How to find the last access date of a database

SQL Script to find database last access date

Sometime we create databases on Test/ Development database servers for temporary uses for short duration and forgot to delete them after the work got completed. As a part of house keeping activity, we need to find out when a database is last accessed in order to find out the databases which are not used since long time and can be a deleted.

To find the last access date of a database in SQL, you can query a system view called sys.dm_db_index_usage_stats. This view provides information about the last time different operations were performed on a database’s indexes.

The following SQL script will show you the last access date for each database by looking at the last time a read or write operation occurred on any of its indexes.

SELECT
DB_NAME(database_id) AS DatabaseName,
MAX(last_user_seek) AS LastUserSeek,
MAX(last_user_scan) AS LastUserScan,
MAX(last_user_lookup) AS LastUserLookup,
MAX(last_user_update) AS LastUserUpdate,
MAX(CASE
WHEN last_user_seek >= last_user_scan AND last_user_seek >= last_user_lookup AND last_user_seek >= last_user_update THEN last_user_seek
WHEN last_user_scan >= last_user_seek AND last_user_scan >= last_user_lookup AND last_user_scan >= last_user_update THEN last_user_scan
WHEN last_user_lookup >= last_user_seek AND last_user_lookup >= last_user_scan AND last_user_lookup >= last_user_update THEN last_user_lookup
ELSE last_user_update
END) AS LastAccessDate
FROM sys.dm_db_index_usage_stats
GROUP BY database_id
ORDER BY LastAccessDate DESC;

Explanation of the Script

sys.dm_db_index_usage_stats: This is a dynamic management view (DMV) in SQL Server that tracks index usage statistics. It includes timestamps for different types of access.

DB_NAME(database_id): This function gets the name of the database from its ID.

last_user_seek, last_user_scan, last_user_lookup, last_user_update: These columns store the last date and time of various user operations.

last_user_seek: The last time a seek operation was performed.

last_user_scan: The last time a scan operation was performed.

last_user_lookup: The last time a lookup operation was performed.

last_user_update: The last time an update (insert, delete, or modify) operation was performed.

MAX(): Since this view can have multiple rows for a single database (one for each index), we use MAX() to find the latest date among all the different access types.

GROUP BY database_id: This groups the results by database, allowing us to get a single row for each database.

LastAccessDate: The CASE statement is used to find the latest date among the four different types of access columns for a given database.

Note: The usage stats are reset when the SQL Server instance is restarted. This script is useful for finding recent access history since the last service restart.