Posts Tagged ‘ 2014 ’

SQL Server – Last Update and Access Times

I periodically review the usage statistics on my databases to determine what is outdated and no longer used. Here’s the easiest way I’ve found to it:

You’re looking for the sys.db_db_index_usage_stats table. It contains a wealth of information and I recommend having a look at it when you get a chance. But for now, we just want to know when the table was last accessed or updated.

(CAVEAT: The counters/datetimes are reset when the SQL Server service is restarted so the information you get is since the last restart, not the last access/update time historically. This means that if you restarted the SQL Server service 10 minutes ago, it will probably look like none of your databases have ever been accessed.)

How to look at last access/updates for a specific table:

USE [MyDatabase]
GO

SELECT
OBJECT_NAME(object_id) as Table_Name,
last_user_update,
last_user_seek,
last_user_scan,
last_user_lookup, *
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID('MyDatabase')
AND OBJECT_NAME(object_id) = 'MySpecificTable'
GO

If you’re looking for the last access/update times for the whole database:

USE [MyDatabase]
GO

SELECT
OBJECT_NAME(object_id) as Table_Name,
last_user_update,
last_user_seek,
last_user_scan,
last_user_lookup, *
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID('MyDatabase')
ORDER BY Table_Name
GO

The system uses all kinds of index operations to keep a count of this information and it’s very handy when you need it. Enjoy.

Advertisements
Advertisements