Last Access Date Time of Stored Procedure and Functions.

Question

Post reply

Last Access Date Time of Stored Procedure and Functions.

Paresh Prajapati

SSCertifiable

Points: 7567
More actions
December 24, 2008 at 1:58 am

#133297

How can i come to know last access date of Procedure and Functions.
Have any idea to know when the user had last access Procedures/Functions.
_____________________________________________________________________________________________________________
Paresh Prajapati
➡ +919924626601
http://paresh-sqldba.blogspot.com/[/url]
LinkedIn | Tweet Me | FaceBook | Brijj

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 1

There is no way to know this unless your have started the trace.

Regards,
Nitin

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

As long as the plan is still loaded into the proc cache, you can somewhat use the DMV's for this. If it wasn't run since the last cache flush or restart of SQL Server, you have lost it and won't be able to tell at all, even with a SQL Trace:

select db_name(eqp.dbid) as [DBName],

object_name(eqp.objectid, dbid) as [ProcedureName],

eqs.last_execution_time

from sys.dm_exec_cached_plans ecp

join sys.dm_exec_query_stats eqs on ecp.plan_handle = eqs.plan_handle

cross apply sys.dm_exec_query_plan(ecp.plan_handle) eqp

where objtype = N'Proc'