SQL query to find the last time a database was accessed.

select d.name, x1 =
(select X1= max(bb.xx) 
from (
    select xx = max(last_user_seek) 
        where max(last_user_seek) is not null 
    union all 
    select xx = max(last_user_scan) 
        where max(last_user_scan) is not null 
    union all 
    select xx = max(last_user_lookup) 
        where max(last_user_lookup) is not null 
    union all 
        select xx = max(last_user_update) 
        where max(last_user_update) is not null) bb) 
FROM master.dbo.sysdatabases d 
left outer join 
sys.dm_db_index_usage_stats s 
on d.dbid= s.database_id 
group by d.name

or a more advanced script I found on the internet.

Which databases are not been used in an instance with quite a few.
SOSidb posted an editted Query from Andrey Shvidky which writes into a temporary table, and then select on the results, comparing anything that has been found with a last used max date, to any remaining databases within sys.databases… the theory being, (and assumption), that these all have NULL last use date and therefore do not show in the results for the main query, and therefore are candidates for further investigation for removal.

SELECT UNPVT.[DataBase], 
       MaxLastUse = MAX(UNPVT.MaxLastUse)
    INTO #tempdatabases
   FROM
    (
       SELECT [DataBase], 
           last_user_seek, 
           last_user_scan, 
           last_user_lookup, 
           last_user_update
      FROM
    (
        SELECT [DataBase] = DB.[name], 
               last_user_seek = MAX(IU.last_user_seek), 
               last_user_scan = MAX(IU.last_user_scan), 
               last_user_lookup = MAX(IU.last_user_lookup), 
               last_user_update = MAX(IU.last_user_update)
        FROM sys.databases AS DB
             INNER JOIN sys.dm_db_index_usage_stats AS IU ON IU.database_id = DB.database_id
        GROUP BY DB.[name]
    ) AS DBIU
) AS P UNPIVOT(MaxLastUse FOR ColumnName IN(last_user_seek, 
                                            last_user_scan, 
                                            last_user_lookup, 
                                            last_user_update)) AS UNPVT
GROUP BY UNPVT.[DataBase]
HAVING MAX(UNPVT.MaxLastUse) IS NOT NULL
ORDER BY MAX(UNPVT.MaxLastUse) ASC;
SELECT name, 
       create_date AS 'Created on', 
       'Not used since last instance start', 
(
    SELECT create_date
    FROM sys.databases
    WHERE database_id = 2
) AS 'Instance start date'
FROM sys.databases
WHERE database_id NOT IN(1, 2, 3, 4)
AND name NOT IN
(
    SELECT [DataBase]
    FROM #tempdatabases
);
DROP TABLE #tempdatabases;

Leave a Reply Cancel reply