SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deleting the tables which are not been used from long time.


Deleting the tables which are not been used from long time.

Author
Message
ksr39
ksr39
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 200
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
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5254 Visits: 4076
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.:-D

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
ksr39
ksr39
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 200
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
pujain
pujain
Old Hand
Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)

Group: General Forum Members
Points: 377 Visits: 241
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)
durai nagarajan
durai nagarajan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1609 Visits: 2775
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26143 Visits: 17539
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 Modens 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)
ksr39
ksr39
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 200
Hi PuJain,

Thank you for the script, when I run the query I found that Last Select is ‘Null’ what does this 'Null' mean.
crmitchell
crmitchell
SSChasing Mays
SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)

Group: General Forum Members
Points: 651 Visits: 1761
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search