How can we find when a database was last used in SQL Server

- How to create a Logon Trigger to control which users are allowed to connect to an Oracle Database - 13 January 2025
- How do we collect the actual execution plan from queries using Extended Event and how do we read its data - 2 December 2024
- How do we find what permissions a user has in an Oracle Database? - 1 November 2024
In the article we will see an easy and quick way to see which databases are in use and which users are currently connected to them.
To see when a database was last accessed in SQL Server, we should see when its most recently accessed index was accessed.
The query to find this information is the following:
SELECT d.name as DBname, last_user_seek = MAX(last_user_seek), last_user_scan = MAX(last_user_scan), last_user_lookup = MAX(last_user_lookup), last_user_update = MAX(last_user_update) FROM sys.databases AS d LEFT JOIN sys.dm_db_index_usage_stats AS i ON i.database_id=d.database_id WHERE d.database_id not in (1,2,3,4) GROUP BY d.name;

Depending on the column we can also understand whether it was simply read (seek, scan, lookup) or updated (update). If all the columns NULL means that the database has not been accessed since the instance was last restarted.
*In the above we should pay special attention as every time the instance of SQL Server is restarted all values become NULL as the information is deleted
Bonus how to see which users are currently active
To see which users are currently active in the databases along with the name of the computer and the name of the program they are using, we run the following query:
select DB_NAME(dbid) as DBname,count(dbid) as Connections,loginame,program_name,hostname from sys.sysprocesses where dbid > 4 --and loginame not like 'NT Service%' --and loginame != 'sa' group by dbid,loginame,program_name,hostname order by dbid,loginame;

Very valuable information