January 17, 2011 at 2:55 pm
Hello All,
I want to purge or archive old tables in my database. Is there a way either through a query or some other method to determine tables in a database that have not been accessed or updated in a while? Of course I will have to determine what "in a while" means. I am wondering if there is a timestamp or some other parameter that can be used to determine if the table is being used.
Thanks in advance for your help!
Ronnie
January 17, 2011 at 3:13 pm
If you query the system table sys.tables, you should have all the info you are looking for.
January 17, 2011 at 4:24 pm
guthey.aditya (1/17/2011)
If you query the system table sys.tables, you should have all the info you are looking for.
Careful, this shows when the table structure was modified, i.e., add a column, delete a column, etc., etc. Not when data was added, updated or deleted.
Others may know, but I do not believe that a record is kept when a SELECT statement is issued against a table. You could start a trace, but what if the table is only accessed once every quarter/year to provide data for a management report.
January 17, 2011 at 4:44 pm
There is no way to determine when the last time a table was accessed in terms of SELECT queries, and unless you are auditing somehow (even with a GETDATE() in a column), you cannot tell when it was last insert/updated/deleted to/from.
If you think you should archive, script the table with permissions export the data somewhere (or save a specific backup), make multiple copies, and let multiple know before you do this.
January 18, 2011 at 7:46 am
Yeah, without a, pick your time period, 6 months/year/decade worth of transaction monitoring, there is no way to know what data was accessed.
If you're interested in what has been access recently (as in, since the last time the cache cleared) you can look at sys.dm_exec_query_stats, but understand, that's only going to show recent access. If the cache cleared a week ago, you won't see queries from prior to that. If cache cleared an hour ago, same deal.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 19, 2011 at 1:41 pm
Thank you very much for the follow up and advice. I wasn't sure if it could be done, but I didn't want to say "No" until I confirmed it.
I appreciate the help from all.
Thanks,
Ronnie
January 19, 2011 at 11:28 pm
You can rename the tables before actually dropping them. For example "DELETED20112010_TableName". If nothing breaks after X period of time you consider safe, you can delete the table. If something does break, rename it back to the original name.
Just make sure you have a backup of the DB in case you need to go back and recover the table.
January 20, 2011 at 4:03 am
Thank you for your post!
So basically, it sounds like this process is manual meaning that I will have to look at each table to determine if I "think" it's not being used.
Doesn't sound like there's a search criteria for identifying those tables.
January 20, 2011 at 5:49 am
Ronnie Jones (1/20/2011)
Thank you for your post!So basically, it sounds like this process is manual meaning that I will have to look at each table to determine if I "think" it's not being used.
Doesn't sound like there's a search criteria for identifying those tables.
Not really. Or to put it more accurately, there's a different search criteria for each application and each database. There is no switch, anywhere, that says, this table was accessed at such & such a time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply