November 27, 2012 at 9:41 am
Hi
I am newly joined database developer in my concern.In my company last three years they are developed one products. They did main mistake developer unwanted they created a so may stored procedure.(more then 5000).but my current front end application only used less procedures.but my product we are having more then 1000 forms. each form each method find out a procedure is very difficult.Any idea is there find out used procedure?
Any Possibility my testing team keep on running front end application ,but i should track what are all the procedures executed?any DMV is available find out currently executed procedure name?please help me.
November 27, 2012 at 11:37 am
Generally, you run a trace (Extended Events in SQL 2008 or later) for a long time, to find that kind of thing.
After all, what if a stored procedure is only used by an annual report? A critical annual report that the CEO of the company wants on his desk on the last Friday in December every year. And you drop the stored procedure because "it hasn't been used this week, so it must not be important". Not good for you when the CEO's report throws an error instead of printing out data.
If your developers use source control properly, you should be able to identify all the current versions of all applications, reports, SSIS packages, etc. Then it will probably be time consuming and boring, but easy, to determine what database objects are called by each.
So, check if your database has any sort of audit log already built into it, that records access by proc. Not likely, but it can be built and maybe someone already did it.
Then, when you find that's not done, check what traces are already running. If it's just the default trace, that won't have what you need. You'll need to set up a trace, and run it for a very long time. Capture what stored procedures are run. Then you can mine the results of that, and get a list of everything that's been in use for the duration of the trace.
- 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
November 27, 2012 at 11:40 am
You can also edit all the procs (slow, I know) to log to a table when they are executed.
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply