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?
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