vd
asked on
Which SQL Server Stored Procedures have been executed
In SQL Server version 2000 or 2005, is there a way I can determine which stored procedures in a database have been executed over the past 6 months, or alternatively, is there something I can do (without modifying the stored procedures themselves) to determine which stored procedures have been executed over the next month ?
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
Guy Hengel [angelIII / a3]
unless you put in place a change in the procedure OR put in place a trace, there is no way to know.
<<In SQL Server version 2000 or 2005, is there a way I can determine which stored procedures in a database have been executed over the past 6 months, or alternatively, is there something I can do (without modifying the stored procedures themselves) to determine which stored procedures have been executed over the next month ?>>
No.
No.
Yes you can
Identifying Unused Objects in a Database
http://www.sqlservercentral.com/columnists/lPeysakhovich/2582.asp
Identifying Unused Objects in a Database
http://www.sqlservercentral.com/columnists/lPeysakhovich/2582.asp
<<Yes you can>>
What does identifying *unused* objects with identifying *all* procedures used over a period of time? Are you saying that *all* procedures are *unused*?
What does identifying *unused* objects with identifying *all* procedures used over a period of time? Are you saying that *all* procedures are *unused*?
Sorry typo, I of course meant
What does identifying *unused* objects has to do with identifying *all* procedures used over a period of time?
What does identifying *unused* objects has to do with identifying *all* procedures used over a period of time?
Did you read the article?
No, there is no "SELECT * FROM sysunusedproc"
It describes a way of detecting unused SPs based on cached execution plan.
No, there is no "SELECT * FROM sysunusedproc"
It describes a way of detecting unused SPs based on cached execution plan.
<<<Did you read the article?>>
Yes and I did not find any answer to the question I have tried to raise.
<<It describes a way of detecting unused SPs based on cached execution plan. >>
No doubt about that but it seems to me that the question is *is there a way I can determine which stored procedures in a database have been executed over the past 6 months* . How does that relate to unused SP's. Are you suggesting that an unused proc is *de facto* the opposite of historically used sp's? What about sp's that have been deleted?
Trying to understand what you're trying to get at...
Regards...
Yes and I did not find any answer to the question I have tried to raise.
<<It describes a way of detecting unused SPs based on cached execution plan. >>
No doubt about that but it seems to me that the question is *is there a way I can determine which stored procedures in a database have been executed over the past 6 months* . How does that relate to unused SP's. Are you suggesting that an unused proc is *de facto* the opposite of historically used sp's? What about sp's that have been deleted?
Trying to understand what you're trying to get at...
Regards...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<<No, you can't immediately detect, what SPs weren't executed over last 6 moths, one month or whateve>>
But the point is to detect that *were* executed in the past *6 monthes* *not* just the sp's that were executed in the past month and the one that were recorded. What gives you the guarantee for instance that the cache was not cleared? what if some sp's became obsolete...
<<No, you can't immediately detect, what SPs weren't executed over last 6 moths, one month or whatever.
At first, cached plans (from SYSCACHEOBJECTS) must be cleared. Now, if SP was executed, there is a record in SYSCACHEOBJECTS. After a month, you could check SYSCACHEOBJECTS. If there's no record for some SP, it wasn't executed. It looks simple to me, unless I'm missing something.>>
Let me get this straight. You are basically saying that the syscacheobjects system tells what sp were ran in the last month from the time the cash was cleared. Right?
But the question states that the user wants to find out what procedures were executed in the past *6 monthes* which makes a huge difference between what you can achieve through the suggested method.
<<It looks simple to me, unless I'm missing something.>>
I think it looks simple only to you ;)
I believe the method suggested may help get some information but I am not convinced it answers the question. So for the moment I'd stick to what angelII and I said. (meaning there is no way to do it unless done with explicit logging)
Regards...
But the point is to detect that *were* executed in the past *6 monthes* *not* just the sp's that were executed in the past month and the one that were recorded. What gives you the guarantee for instance that the cache was not cleared? what if some sp's became obsolete...
<<No, you can't immediately detect, what SPs weren't executed over last 6 moths, one month or whatever.
At first, cached plans (from SYSCACHEOBJECTS) must be cleared. Now, if SP was executed, there is a record in SYSCACHEOBJECTS. After a month, you could check SYSCACHEOBJECTS. If there's no record for some SP, it wasn't executed. It looks simple to me, unless I'm missing something.>>
Let me get this straight. You are basically saying that the syscacheobjects system tells what sp were ran in the last month from the time the cash was cleared. Right?
But the question states that the user wants to find out what procedures were executed in the past *6 monthes* which makes a huge difference between what you can achieve through the suggested method.
<<It looks simple to me, unless I'm missing something.>>
I think it looks simple only to you ;)
I believe the method suggested may help get some information but I am not convinced it answers the question. So for the moment I'd stick to what angelII and I said. (meaning there is no way to do it unless done with explicit logging)
Regards...