Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Deleting the tables which are not been used from long time. Expand / Collapse
Author
Message
Posted Wednesday, October 10, 2012 2:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 20, 2014 9:58 AM
Points: 37, Visits: 159
HI 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 advance
Post #1370753
Posted Wednesday, October 10, 2012 2:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
ksr39 (10/10/2012)
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.
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.


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1370770
Posted Wednesday, October 10, 2012 3:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 20, 2014 9:58 AM
Points: 37, Visits: 159
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
Post #1370796
Posted Wednesday, October 10, 2012 6:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 4:00 AM
Points: 299, Visits: 231
you 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)
Post #1370879
Posted Wednesday, October 10, 2012 8:10 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
ksr39 (10/10/2012)
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


if an application is depreciated why the relavent tables not deleted from production server or any debugging happening directly on production server?



Regards
Durai Nagarajan
Post #1370942
Posted Wednesday, October 10, 2012 8:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 13,302, Visits: 12,168
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?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1370963
Posted Wednesday, October 10, 2012 11:13 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 20, 2014 9:58 AM
Points: 37, Visits: 159
Hi PuJain,

Thank you for the script, when I run the query I found that Last Select is ‘Null’ what does this 'Null' mean.
Post #1371213
Posted Friday, October 12, 2012 2:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:21 AM
Points: 206, Visits: 838
Sean Lange (10/10/2012)
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?


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.
Post #1371935
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse