October 25, 2007 at 1:10 pm
Hi Guys,
Does somebody know how I can find the last time a proc was executed, or maybe a historic of execution (timestamp of each time the proc was executed).
I have procs that I believe are not used anymore, but it is hard to say for sure if they have been used or not.
Thanks a lot,
Luiz.
October 26, 2007 at 12:55 am
If the procedure has been executed recently, it may be in the execution plan cache. If that's the case you can find the time the plan was created and the time it was last run.
Other than that, there's no way I know of, other than having the proc insert into an execution history when it starts.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 26, 2007 at 1:07 am
For the exec plan cache query, run this in the db that has the stored procs you're interested in. DB will need to be in compat mode 90
SELECT creation_time, last_execution_time, OBJECT_NAME(OBJECT_ID)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE objectid = OBJECT_ID('[MyProcNameHere]') AND dbid = DB_ID()
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 26, 2007 at 1:15 am
try to figure out from these blogs ....
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply