Monitor Database Activity

If we want to monitor when the last action performed on the database? Or to find database last accessed time? The below query is used to monitor those kind of activities on database.

Note - Create a job which runs every 1 hour with the below query and insert data into a temporary table, later you can do a group by based on database name and host name to identify who used the database recently.

Query

SELECT DISTINCT db_name(sp.dbid) as sysdbname,
sp.loginame, sp.hostname, sp.login_time, sp.last_batch, sp.status, sp.cmd
FROM master..sysprocesses sp