Post reply

suggest me a good solution.

  • July 27, 2009 at 10:53 pm

    #224041

    Can any one suggest me a good solution to find the tables and procedures whch are unused (example from last 2 months or 3) in SQL Server 2005 database . Are there any tools ?

    I am in aprocess of dB Clean up activity so , could anyone please give a good advice

    Any ideas please.... Fast help is appreciated...!

    Cheers,
    - Win.

    " Have a great day "

  • July 27, 2009 at 11:55 pm

    #1030842

    you can get information related with table usage from this : sys.dm_db_index_usage_stats

  • July 28, 2009 at 1:24 am

    #1030880

    sys.dm_db_index_usage_stats only holds info since the last start of SQL Server

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 28, 2009 at 1:35 am

    #1030881

    Hi

    If possible run a trace and check which all procedures are used. You need to run the trace for a time period where you are sure that all the procs in use are going to be executed.

    Another manual way of doing this take a set of procedures and check in the code whether these procedures are used. If you have a very large database then this activity can be split over a few days. Advantage of this is that you can continue this process after the DB clean up is done so that unwanted procs/tables are removed from DB at regular intervals.

    Dont forget to take backups.

    "Keep Trying"

  • July 28, 2009 at 5:06 am

    #1030953

    GilaMonster (7/28/2009)

    sys.dm_db_index_usage_stats only holds info since the last start of SQL Server

    I would like to add up here that all the DMV modules in SQL Server 2005 stored data only since last time SQL Server starts.

    -Forum Etiquette: How to post Performance Problems[/url]

    -Forum Etiquette: How to post data/code to get the best help [/url]

  • July 28, 2009 at 5:42 am

    #1030964

    there is just no way to be sure. You have to know all the processes that access the system inside & out. For example, even if you monitor the system for three months, collecting trace information (the best approach, posted earlier), you may not get the quarterly or bi-annual process that cleans up something using some table or function, not to mention fiscal or year-end processes...

    This is one of the hardest things to do, cleaning out junk from a database. Be careful. Keep a log of what you drop and good backups of the data & structures that get dropped as well. You might need them.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

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

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