When Was the Database was Last Used?

In some database systems, administrators may need determine which databases are frequently used.

Sp_who2 give you list of actions running currently on the serve and it gives you a column with the database name.  In addition, the activity monitor will provide the same details and the user will have the option of filtering the required databases. However, both these options will only show current connections at this moment and will not provide historical data.

The following query will return which are provided from sp_who2.

SELECT @@ServerName AS serverName,
  NAME AS DatabaseName
 ,COUNT(STATUS) AS NumberofConnection
 ,GETDATE() AS Time
FROM sys.databases sd
LEFT JOIN sysprocesses sp ON sd.database_id = sp.dbid
WHERE database_id NOT BETWEEN 1 AND 4
GROUP BY NAME

However, this also returns the connections which are running at this moment.

However, you can combine this query into a SQL Server Agent Job to run on given schedule. When this data is inserted to a table, that table can be analysed to find out what databases are frequently used. 

Though this accomplishes the requiremnt, although you will need to schedule the job in advance.  There is another workaround with a few limitations :

SELECT  DB_NAME(database_id) as DBName,
       COALESCE(MAX(last_user_seek),  MAX(last_user_scan) , MAX(last_user_lookup) ,   MAX(last_user_update)) LassAccessDate
FROM sys.dm_db_index_usage_stats
WHERE database_id NOT BETWEEN 1 AND 4
Group By DB_NAME(database_id)
Order by DB_NAME(database_id)

sys.dm_db_index_usage_stats DMV returns counts of different types of index operations and the time each type of operation was last performed in SQL Server. Using this method, it is assumed that users are accessing the database via indexes. Also, this does not  count operations such as table creations, table alterations, login and user creations etc.

]]>