Post reply

Strategy for finding and removing redundant objects from a SQL Server 2000 database

  • October 6, 2004 at 7:44 am

    #163431

    I am looking for some advice on the best way of finding and removing redundant objects from one of my sql server 2000 databases. What would be the best way of going about this task?

    Regards

    Justin

  • October 6, 2004 at 7:02 pm

    #525695

    Justin,

    The answer would depend on what you mean by "redundant objects".   Do you mean tables with duplicate rows, or more than one index with the same columns on the same table -  or did you mean the same object in more than one database on the same server?  Two objects can't have the same name in the same database.   There are queries that can help with any of these.

    Danette 

  • October 7, 2004 at 2:23 am

    #525723

    Thanks Danette for you reply, I actually mean't objects that were no longer required in the database, such as Views, store procedures even tables that are no longer used, rather then data issues.

    Is there a way of identifying objects that have not been used for a period of time?

    Regards

    Justin

  • October 7, 2004 at 6:08 am

    #525735

    That is much tougher!  I am always wary of making anyassumptions about that something is never used, because things could be used very infrequently, but be very necessary.   Is your database an online transaction processing (OLTP) system or more of reporting database?  You could set up Profiler to look for a specific stored procedure for a while, but that will add overhead to your system and slow performance.

    However, if you want to find stored procedures and views that depend on a table you think is not used, or you know you have dropped, you could do this query in the database and any other database that could possible reference it:

    select o.name "ObjectName", c.text  from dbo.sysobjects o, dbo.syscomments c where o.id = c.id and c.text like '%tablename%'

    I hope that helps.

    Danette

     

  • October 7, 2004 at 9:31 am

    #525783

    Profiler is really the only good way of doing this. Be sure that if you remove stuff, you archive it so that if 6 months from now you find out that it's needed, you can restore it.

    Be wary of this, thre might be objects used for one time a year, like end of year processing, and you might not be aware. I like cleaning things up, but be very careful and ocnservative.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply