Recently Used Tables and Databases

Recently developed these queries to assist with the evaluation of development or sandbox environments.  Sometimes disk space demands that old projects be cleared out, and managers/admins want to know what has and hasn't been used recently.

These queries are built around the dm_db_index_usage_stats DMV and use the last_user_scan, last_user_seek, and last_user_update dates to determine the databases and tables that have actually been used recently.  This is done at the index level.



Someone logging into a database but reading/writing nothing would not register here, which may be a good thing.

Note that these queries are only worthwhile if the server and the databases have been in place for a while.  The dates reset when the SQL service restarts. The dates could also be reset if the database is detached/attached, or when indexes are changed.

Enabling the AUTO_CLOSE feature will also prevent this query from being useful to you - a case statement was added to prevent this unwise setting from skewing your results.

--last user-accessed time of objects in the current database contextselect
Object = s.name + '.' + o.name
, o.object_id, o.create_date, LastAccessed = CASE WHEN d.is_auto_close_on = 1 THEN 'AUTO_CLOSE is on. Access datetimes are unreliable'ELSE convert(varchar(30), MAX(COALESCE(last_user_scan, last_user_seek, last_user_update)), 120 ) ENDfrom sys.dm_db_index_usage_stats usinner join sys.objects o on us.object_id = o.object_idinner join sys.schemas s on o.schema_id = s.schema_idinner join sys.databases d on d.database_id = us.database_idwhere us.database_id = DB_ID()and o.is_ms_shipped = 0
group by s.name + '.' + o.name, o.object_id, o.create_date, d.is_auto_close_onorder by LastAccessed asc, Object asc

--last user-accessed databases on the serverselect d.name, Compatibility = case compatibility_level when 70 then '7.0' when 80 then '2000' when 90 then '2005' when 100 then '2008' when 110 then '2012' end, database_create_date = create_date, SQLServer_Start_Time
, LastAccessed = CASE WHEN d.is_auto_close_on = 1 THEN 'AUTO_CLOSE is on. Access datetimes are unreliable'ELSE convert(varchar(30), MAX(COALESCE(last_user_scan, last_user_seek, last_user_update)), 120 ) ENDfrom sys.databases d
left outer join sys.dm_db_index_usage_stats us
on d.database_id = us.database_idcross apply sys.dm_os_sys_infowhere d.database_id > 4group by d.name,compatibility_level, create_date, SQLServer_Start_Time, d.is_auto_close_onorder by LastAccessed asc, d.name asc