Solved

Track SQL Server 2005 StoredProcedure usage

Posted on 2013-01-11
3
446 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 43

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

617 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