InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 22:22:22 GMT20http://www.sqlservercentral.com/Forums/Topic1370753-146-1.aspx[quote][b]Sean Lange (10/10/2012)[/b][hr]Be careful with approach. Deleting tables just because they haven't been used in a couple months can very likely come back to bite you. What about reports that are only run once a year or other types of similar processes that are very important but not frequent?[/quote]Definitely this.I would not be comfortable with this anywhere near DR.If the worst happens and you need to recover its much better to find you have recoverred something you no longer need than to find that vital piece of data used for e.g. your annual tax return is missing.By all means check through your databases to identify items which are no longer needed but dont mix this up with disaster recovery.Fri, 12 Oct 2012 02:13:23 GMTcrmitchellhttp://www.sqlservercentral.com/Forums/Topic1370753-146-1.aspxHi PuJain,Thank you for the script, when I run the query I found that Last Select is ‘Null’ what does this 'Null' mean. Wed, 10 Oct 2012 23:13:37 GMTksr39http://www.sqlservercentral.com/Forums/Topic1370753-146-1.aspxBe careful with approach. Deleting tables just because they haven't been used in a couple months can very likely come back to bite you. What about reports that are only run once a year or other types of similar processes that are very important but not frequent?Wed, 10 Oct 2012 08:26:22 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1370753-146-1.aspx[quote][b]ksr39 (10/10/2012)[/b][hr]Thank You Bhuvnesh for the quick reply,But it’s difficult to find the table which is not been used more than a month as my server was rebooted since 65 days. If I can get an exact table names so that I can change their names it’s a good thing but when I am not sure with the table name then it will be over work in doing this. So is there any way other than this where I can get the tables info which are not been used more than a month or more.Thank you in Advance[/quote]if an application is depreciated why the relavent tables not deleted from production server or any debugging happening directly on production server?Wed, 10 Oct 2012 08:10:17 GMTdurai nagarajanhttp://www.sqlservercentral.com/Forums/Topic1370753-146-1.aspxyou can use the below script to find when the table got accessed last time WITH LastActivity (ObjectID, LastAction) AS ( SELECT object_id AS TableName, last_user_seek as LastAction FROM sys.dm_db_index_usage_stats u WHERE database_id = db_id('DBNAME') UNION SELECT object_id AS TableName, last_user_scan as LastAction FROM sys.dm_db_index_usage_stats u WHERE database_id = db_id('DBNAME') UNION SELECT object_id AS TableName, last_user_lookup as LastAction FROM sys.dm_db_index_usage_stats u WHERE database_id = db_id('DBNAme') ) SELECT OBJECT_NAME(so.object_id) AS TableName, MAX(la.LastAction) as LastSelect FROM sys.objects so LEFT JOIN LastActivity la on so.object_id = la.ObjectID WHERE so.type = 'U' AND so.object_id > 100 GROUP BY OBJECT_NAME(so.object_id) ORDER BY OBJECT_NAME(so.object_id)Wed, 10 Oct 2012 06:17:07 GMTpujainhttp://www.sqlservercentral.com/Forums/Topic1370753-146-1.aspxThank You Bhuvnesh for the quick reply,But it’s difficult to find the table which is not been used more than a month as my server was rebooted since 65 days. If I can get an exact table names so that I can change their names it’s a good thing but when I am not sure with the table name then it will be over work in doing this. So is there any way other than this where I can get the tables info which are not been used more than a month or more.Thank you in AdvanceWed, 10 Oct 2012 03:40:14 GMTksr39http://www.sqlservercentral.com/Forums/Topic1370753-146-1.aspx[quote][b]ksr39 (10/10/2012)[/b][hr] I tried but couldn’t find the exact way. So, please help me in finding the exact tables which are not been used. And more over how to find the tables which are been used in Sp’s and Views, Where I found some tables which are not in the code but they are available in database. [/quote] there is no straight forward way to find those tables only secured approach is rename the tables which you think are no more in use like "test" to "dm_test", you can easily find that when and where this particular table for used (error will be there if it stil in use ) another thing if you dont get any issue then it means that table is not in use.:-DWed, 10 Oct 2012 02:55:27 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1370753-146-1.aspxHI Experts,Actually we are planning to replicate the prod data to our DR center. So, thinking to replicate only that data which are used, so I need a help in getting the tables which are not been used so that I can delete them, and replicate only those tables which are in use. I tried but couldn’t find the exact way. So, please help me in finding the exact tables which are not been used. And more over how to find the tables which are been used in Sp’s and Views, Where I found some tables which are not in the code but they are available in database. Thank you in advanceWed, 10 Oct 2012 02:12:45 GMTksr39