?
Solved

Which SQL Server Stored Procedures have been executed

Posted on 2007-04-03
9
Medium Priority
?
190 Views
Last Modified: 2010-03-19
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  ?  
0
Comment
Question by:vd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18848478
unless you put in place a change in the procedure OR put in place a trace, there is no way to know.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 18849398
<<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.
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 18849902
Yes you can
         
Identifying Unused Objects in a Database
http://www.sqlservercentral.com/columnists/lPeysakhovich/2582.asp
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 18849968
<<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*?

0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 18849975
Sorry typo, I of course meant
What does identifying *unused* objects has to do with identifying *all* procedures used over a period of time?
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 18850027
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.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 18850780
<<<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...
0
 
LVL 19

Accepted Solution

by:
Rimvis earned 500 total points
ID: 18851059
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.

>>What about sp's that have been deleted
Well, then you can't detect, if it was ever executed or not. But I don't this it is a big problem.

0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 18851220
<<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...
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question