Table was last accessed in SQL Server database

Question

Hi All,

We have SQL Server DB with 1000+ tables. We are planning to rename unused tables as a _temp and delete _temp tables from DB after few weeks If there is no issues/complaints from users. This exercise is to reduce DB size from TB to GB and reduce the complexity.

I found below query to check when was last table accessed via stored proc/functions/custom script...But below script is cleaned each time SQL Server is restarted.. Please suggest how to list tables that are not used in last 6 months.

select [schema_name], 
       table_name, 
       max(last_access) as last_access 
from(
    select schema_name(schema_id) as schema_name,
           name as table_name,
           (select max(last_access) 
            from (values(last_user_seek),
                        (last_user_scan),
                        (last_user_lookup), 
                        (last_user_update)) as tmp(last_access))
                as last_access
from sys.dm_db_index_usage_stats sta
join sys.objects obj
     on obj.object_id = sta.object_id
     and obj.type = 'U'
     and sta.database_id = DB_ID()
) usage
group by schema_name, 
         table_name
order by last_access desc;

Answer 1

Hi @PraveenKumar

How about save the query result into another table and created a SQL Job to run this query every specific time.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

below script is cleaned each time SQL Server is restarted.

Sure, such DMV values are not peristed, you have to log/persist the values on your own.

Please suggest how to list tables that are not used in last 6 months.

As before, SQL Server don't log it, you have to implement an own logging.

There is no other, reliable way.

Answer 3

I can't think of a good solution. The method Olaf mentioned is feasible, but its accuracy is not guaranteed. Furthermore, I would like to remind you that your goal is to delete useless tables, but this method can only remove tables with indexes; tables without indexes will never be included, meaning they will be left out.