July 27, 2009 at 10:53 pm
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
you can get information related with table usage from this : sys.dm_db_index_usage_stats
July 28, 2009 at 1:24 am
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
July 28, 2009 at 1:35 am
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
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.
July 28, 2009 at 5:42 am
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