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
FROM sys.databases sd
LEFT JOIN sysprocesses sp ON sd.database_id = sp.dbid
WHERE database_id NOT BETWEEN 1 AND 4

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

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.


No comments yet... Be the first to leave a reply!