Solved

Track SQL Server 2005 StoredProcedure usage

Posted on 2013-01-11
3
435 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 19

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 400 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 42

Assisted Solution

by:Eugene Z
Eugene Z earned 100 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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

763 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