So we're exploring our options in terms of migrating our SQL Server database to another engine. A question that we've been asked when talking with other providers is how much of our stored information could potentially live in cold storage / archives.

Is there a way to figure this out through SSMS or any other third party tools?

CodePudding user response：

You can use sys.dm_db_index_usage_stats view, but it is cleared on server restart, so might not be sufficiently reliable.

Look at all datetime columns, eg.

SELECT t.name,
  (SELECT Max(v) 
   FROM (VALUES (last_user_seek), (last_user_scan), (last_user_lookup),   (last_user_update)) AS value(v)) as [MaxDate]
FROM sys.dm_db_index_usage_stats us 
join sys.tables t
on t.object_id = us.object_id