How to Obtain Call History of a Stored Procedure/UDF
-
I know in the past this wasn't possible but maybe that's changed as SQL Server evolved.
Is it possible to get execution history for a Stored Proc and or a UDF, specifically when the cod was called or executed? If yes can what is the minimal version of SQL Server required to do it?
Thanks
Kindest Regards,
Just say No to Facebook! -
Yes... that can be done and the methods have been available for quite a while. The problem is that it's arduous for the server to record information every time a stored procedure is executed and ever worse for UDFs. With that, I have to ask the now famous Brent Ozar question, what is the problem that you're actually trying to solve?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Jeff. I should have been more specific. I know you can setup things in advance to get this, I was looking more for something that is built in that already does that which is passive. The problem I'm tackling is trying to determine if an SP was called and when b/c changes were made to data, the kind of changes found within this 1 SP, but there's been no actions or the like that would have called the SP; a kind of he said she said but with code. If SQL Server was keeping track of when an SP get's called then I could rule in/out if the SP was teh sources of data changes or if it was something else,
Thanks J
Kindest Regards,
Just say No to Facebook! -
Can't QueryStore help out ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
There is nothing 'out of the box' that monitors calls to procedures/functions and logs those somewhere. If you have not setup an extended event, profiler trace - or possibly query stored (depends on the settings - I believe), then you are out of luck.
The best you can possibly do is to identify any/all methods of accessing that database that have the ability to make the changes - and show that any of those could be the culprit. Any database user that has write access to the table(s) - or execute access to the procedure/function - would be candidates.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - Johan Bijnens wrote:
Can't QueryStore help out ?
I will be damned! That is a good idea! Unfortunately it wasn't enabled so it can't help this time but I checked and we have the necessary permission's to enabled it and use it so it's a future tool. I didn't think we could access it as we have only DBO level access but we can. From what I read as long as there's room to record the query store will keep the history of when an SP was executed along with a lot of stats on it but the run history is most important. We don't deal with tuning or optimization behind tuning individual queries used in custom reports.
Thanks
Kindest Regards,
Just say No to Facebook!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply