Solved

Track SQL Server 2005 StoredProcedure usage

Posted on 2013-01-11
3
425 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
3 Comments
 
LVL 19

Expert Comment

by:strivoli
Comment Utility
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
Comment Utility
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:EugeneZ
EugeneZ earned 100 total points
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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 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…

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now