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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply