Forum Discussion
How to find Unused Tables or Tables not accessed by any process in DB.
Hi, Thanks for your reply, but the view sys.dm_db_index_usage_stats was not storing its data after the SQL Server restart. Unfortunately my server restarted on 28th of this month and i am facing challenges in tracking details of scan, update of tables in my DB. The view only has data after 28th May 2021.
Is there any way to get or access the old data in view before server restart.
your time and help is appreciated.
but the view sys.dm_db_index_usage_stats was not storing its data after the SQL Server restart.
Right, the values of the DMV are not persisted, one reason why I wrote "not that easy".
You can rename some of the table where you think, they may are not used and wait for a month or two if an application errors out.
- Got it. Thanks for your time.
I followed other work around, created a SQL Job and copied the data in index view every 3 hours to other temp table, so my problem is solved and able to find only useful tables.
Stay Safe.Hello, can you share your solution i'm interrested into doing the same thing because i'm not able to clearly identify wich tables are used or not in some databases since the server restart.
Thanks 🙂Please find the below view where usage data is stored.
sys.dm_db_index_usage_stats
You can take this view and filter out which databases you are interested and store this information in other table. This will ensure even the SQL server restarts we have the data in our backup table.
Note : The temporary table should full outer join this view, even the view is empty this will not effect our temp tables data. Hope this will help you.
Thank You.
