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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Deleting the tables which are not been used...

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

Rate Topic

Display Mode

Topic Options

Author

Message

ksr39

Posted Wednesday, October 10, 2012 2:12 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, November 29, 2012 1:48 AM
Points: 34, Visits: 142

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

Bhuvnesh

Posted Wednesday, October 10, 2012 2:55 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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. BigGrin

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1370770

ksr39

Posted Wednesday, October 10, 2012 3:40 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, November 29, 2012 1:48 AM
Points: 34, Visits: 142

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

pujain

Posted Wednesday, October 10, 2012 6:17 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 6:00 AM
Points: 268, Visits: 188

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

durai nagarajan

Posted Wednesday, October 10, 2012 8:10 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:04 PM
Points: 853, Visits: 2,101

ksr39 (10/10/2012)

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

Sean Lange

Posted Wednesday, October 10, 2012 8:26 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 8,592, Visits: 8,233

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

Post #1370963

ksr39

Posted Wednesday, October 10, 2012 11:13 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, November 29, 2012 1:48 AM
Points: 34, Visits: 142

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

crmitchell

Posted Friday, October 12, 2012 2:13 AM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 1:54 AM
Points: 48, Visits: 199

Sean Lange (10/10/2012)

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 »

Permissions