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

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
• How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
• How can we get email whenever PGA memory exceeds a limit in Oracle database - 1 March 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;