Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 461
  • Last Modified:

Track SQL Server 2005 StoredProcedure usage

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
alcindor
Asked:
alcindor
2 Solutions
 
strivoliCommented:
Run the Profiler with an appropriate filter for a time long enough (days? weeks?).
0
 
Saurabh BhadauriaCommented:
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
 
Eugene ZCommented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now