Recently Used Tables and Databases

2012/05/24

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