August 19, 2009 at 12:23 pm
Hi
I've inherited a large db with over 600 stored procedures and countless functions, i am pretty sure that they are not all being used. Is there any way to find out when was the last time a sproc has been used.
I am on sql2005, and I know that there is the dmv sys.dm_exec_query_stats, however this will only show me stuff in the cache. otherwise if the sproc gets executed it will be removed from the view.
any other suggestions are welcomed.
thanks
AJ
August 19, 2009 at 12:43 pm
Run a trace for a while. Track batch completions. That'll give you a very good idea of what's being used and how.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 19, 2009 at 1:08 pm
there's the technology way and the human-interaction way (talk to developers).
They both have a good chance of failing you.
The technology way I can recommend is to run it as a server-side trace with minimal events filtered down as far as it can be filtered. Save the output to a file and allow appropriate rollovers/file size.
script out the trace so you can run it again later, maybe even as a scheduled job with start/stop times.
pump it into a table using fn_trace_gettable() and query to your heart's content.
It will at least show you the ones that you know are being used...
~BOT
Craig Outcalt
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply