473,857 Members | 2,017 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to check when a stored procedure was last called/executed

Hi
Our SQL server has a lot of stored procedures and we want to get some
cleaning up to be done. We want to delete the ones that have been not
run for like 2-3 months. How exactly will i find out which ones to
delete. Enterprise manager only seesm to give the "Create Date"
How exactly can I find the last called date ! I guess you could write a
query for that ! but how ???

P.S I dont want to run a trace for 1 months and see what stored
procedures are not being used.

Jul 23 '05 #1
7 7805

"bajwa" <mo*****@gmail. com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .
Hi
Our SQL server has a lot of stored procedures and we want to get some
cleaning up to be done. We want to delete the ones that have been not
run for like 2-3 months. How exactly will i find out which ones to
delete. Enterprise manager only seesm to give the "Create Date"
How exactly can I find the last called date ! I guess you could write a
query for that ! but how ???

P.S I dont want to run a trace for 1 months and see what stored
procedures are not being used.


There's no record in the system tables of when a proc is executed, so you
would have to either run a trace, or modify the code of every proc to INSERT
into a log table when it runs. You could also look at third-party audit
tools, some of which can examine the transaction log directly
(http://www.lumigent.com for example).

Simon
Jul 23 '05 #2
How much overhead are we talking about when we modify the code to
INSERT
into a log table when it runs ???
Would it decrease performance at all. My guess is that its a simple
insert so should not have any big performance time decrease, but i
still need to be sure.

Jul 23 '05 #3
Have you thought about doing the log idea but instead of modifying the
stored procedure just create a trigger for when a stored procederure is
run to do the insert

Jul 23 '05 #4
that seems to be an excellent idea.That way I wont have to open up all
the numerous stored procedures just to paste that one line. Do you have
the code for such a trigger if you use it right now ?

Jul 23 '05 #5
bajwa (mo*****@gmail. com) writes:
Our SQL server has a lot of stored procedures and we want to get some
cleaning up to be done. We want to delete the ones that have been not
run for like 2-3 months. How exactly will i find out which ones to
delete. Enterprise manager only seesm to give the "Create Date"
How exactly can I find the last called date ! I guess you could write a
query for that ! but how ???


There is no information about this in SQL Server.

In any case, I would never delete a stored procedure just because it
has not been run in 2-3 months. 2-3 years maybe.

The proper way to handle this is to have all your code under version
control. Then you can search the version-control system for references
to suspects.

Of course, a more brutal way is rename the suspects, and see if how many
errors you get...

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6

"Dan Gidman" <da*******@gmai l.com> wrote in message
news:11******** *************@l 41g2000cwc.goog legroups.com...
Have you thought about doing the log idea but instead of modifying the
stored procedure just create a trigger for when a stored procederure is
run to do the insert


The problem with that approach is that @@PROCID inside a trigger returns the
trigger's object ID, not the ID of the stored procedure (if indeed it was
triggered by a stored procedure at all). As far as I know, there's no way to
get the 'triggering' code from inside a trigger - using fn_get_sql() shows
the trigger code.

So you wouldn't know which stored procedure did an INSERT, or even if it was
a stored procedure. Unless of course you know exactly which procedure
INSERTs into each table, or at least have a good enough idea to make your
results accurate enough for your needs.

Simon
Jul 23 '05 #7

"bajwa" <mo*****@gmail. com> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.com...
How much overhead are we talking about when we modify the code to
INSERT
into a log table when it runs ???
Would it decrease performance at all. My guess is that its a simple
insert so should not have any big performance time decrease, but i
still need to be sure.


I have no idea - it would depend entirely on your database design and the
load at the time. A simple INSERT like this should have minimal impact:

insert into dbo.StoredProcL og (ProcName, ExecutionTime)
values (object_name(@@ procid), current_timesta mp)

But as with any change to your database code, you should try it in a test
environment first.

Simon
Jul 23 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1330
by: M Wells | last post by:
Hi All, I don't know if this is a truly esoteric question, or not, but I'm wondering how (or even if) you handle a timeout on a transaction within a stored procedure executed in a Stored Procedure? A theoretical example of the stored procedure (sadly, I'm not allowed to post the actual code):
9
3727
by: heruti | last post by:
Hi all... I've been stumped by this for days. Bit of ASP code: (IIS) Set LocalConn = CreateObject("ADODB.Connection") LocalConn.CursorLocation = adUseClient LocalConn.CommandTimeout = 0 LocalConn.Open sConnStringSO (SQL Server 2000, irrelevant what it is, see below). LocalConn.Errors.Clear
4
4408
by: Nyul | last post by:
Gurus, I have a verb big problem which I'm unable to explain. We have a DB2 V6.1.0 on AIX 4.3 I want to make a C stored procedure which at the end will be called by a PHP script. The development server was an UDB V6.1.0 on W2K. Everything went well. I was able to call the stored proc from C and from Delphi.
2
2406
by: Tim.D | last post by:
Hello people, Thanks to Serge, PM, Knut and a host of others I have now successfully completed my very first SQL Stored Procedure. Takes some 13mins or so to run, but that was expected as it does a fairly significant number of small single select and inserts from various cursors. I am very pleased with the results and can find no errors after a few days debugging a couple of never ending loops. Interestingly though, while processing...
1
2809
by: mike | last post by:
If I try and do a "SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1" after I have run a stored procedure in DB2 version 7.2, I get the last generated Key before the CallableStatement was executed in the transaction and not the key generated in my stored procedure. Is there a way to set the IDENTITY_VAL_LOCAL or have the stored procedure generated key show up in my IDENTITY_VAL_LOCAL value? Thanks Mike
13
5033
by: Kantha | last post by:
Hi all, I have declared an Union as follows typedef union { struct interrupt_bits { unsigned char c_int_hs_fs_status : 1, c_setup_intflag : 1,
14
4608
by: jehugaleahsa | last post by:
Hello: I am working with Oracle .NET Stored Procedures. I would like to know how to return the results of a SELECT statement. I have tried returning a OracleRefCursor and a DataTable, but nothing seems to work. What data type must I return for this to be accepted as .NET stored procedure?
0
1232
by: DR | last post by:
what are the memory caps for threads running as a CLR stored procedure executed by sql server 2005? is it limited by OS only or also by sql servers memory limits? e.g. lets say my clr stored procedure is executed by sql server 2005 then it creates 10 threads and each thread builds a giant array on the stack, is the limits of each array limited by sql server or the os? do these clr stored procedures run inside the sql server address space or...
3
7356
by: lenygold via DBMonster.com | last post by:
Thank you very much SERGE for your help. I found example in Graeme Birchall COOKBOOK wich i think exactly what i need for SQL check in triggers: • User query joins to table function - sends DML or DDL statement to be executed. • Table function calls stored procedure - sends statement to be executed. • Stored procedure executes statement. • Stored procedure returns SQLCODE of statement to the table function.
0
9923
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, well explore What is ONU, What Is Router, ONU & Routers main usage, and What is the difference between ONU and Router. Lets take a closer look ! Part I. Meaning of...
0
9767
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10709
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10394
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9544
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development projectplanning, coding, testing, and deploymentwithout human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7939
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupr who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5773
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4592
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4189
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.