Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Track SQL Server 2005 StoredProcedure usage

Posted on 2013-01-11
3
Medium Priority
?
454 Views
Last Modified: 2013-01-13
I have developed a database over several years with a large number of stored procedures, some of which are no longer used but I am unable to easily determine which procedures are no loger used.  Several different applications developed over the same period use these procedures.
I wish to clean out any stored procedures that are no longer used, to assist me in identifying such stored procedures, I would like to find out when each stored procedure was last run. Is there a way to find out when each stored procedure was last run. It is not an option to add code to every stored procedure to update a log file.
Can I obtain this information from the database logs somehow?

Thanks,

Roger
0
Comment
Question by:alcindor
[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
3 Comments
 
LVL 20

Expert Comment

by:strivoli
ID: 38767118
Run the Profiler with an appropriate filter for a time long enough (days? weeks?).
0
 
LVL 12

Accepted Solution

by:
Saurabh Bhadauria earned 1600 total points
ID: 38767130
I Guess there is no exact mechanism to find out the uses history...but below DMV may give you some information

sys.dm_exec_procedure_stats    it returns information about cached stored procedure plan.. so if you SP was executed in short time then it will let you know..

select b.name, a.last_execution_time
from sys.dm_exec_procedure_stats a
inner join sys.objects b on a.object_id = b.object_id
where DB_NAME(a.database_ID) = 'MyDatabase'


Other wise below link have some hint...
http://stackoverflow.com/questions/456080/usage-history-of-stored-procedures-in-sql-server-2008

Thanks,
Saurabh
0
 
LVL 43

Assisted Solution

by:Eugene Z
Eugene Z earned 400 total points
ID: 38767378
additionally to above posted solutions: you can open sp and see what it is doing: and try to track down to a table to see if it gets new data that should be supplied by proc.
also, if you feel ok about it-- rename the proc and wait for  call-
if after some time 1-5 weeks - nobody complained - script the proc (just in case) and delete
--

one more: from http://stackoverflow.com/questions/130753/last-time-a-stored-procedure-was-executed

---
If a stored procedure is still in the procedure cache, you can find the last time it was executed by querying the sys.dm_exec_query_stats DMV. In this example, I also cross apply to the sys.dm_exec_query_plan DMF in order to qualify the object id:
declare @proc_nm sysname

-- select the procedure name here

set @proc_nm = 'usp_test'

select s.last_execution_time
from sys.dm_exec_query_stats s
cross apply sys.dm_exec_query_plan (s.plan_handle) p
where object_name(p.objectid, db_id('AdventureWorks')) = @proc_nm
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

722 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