last time a particular SP was executed? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

last time a particular SP was executed?

Author

Topic

nhuanlieu
Starting Member

27 Posts

Posted - 2009-03-26 : 12:23:04

Hi SQL pros, how to I determine the last time a particular SP was executed in SQL Server 2005?

Thanks!

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-03-27 : 02:49:00

you can get last execution time only if information is in cache

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.TEXT LIKE '%OBJECTNAME%'
ORDER BY deqs.last_execution_time DESC

---A more detailed information----

SELECT OBJECT_NAME(sys.dm_exec_sql_text.objectid),
sys.dm_exec_query_stats.*
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text (sys.dm_exec_query_stats.sql_handle)
WHERE sys.dm_exec_sql_text.dbid = db_id()
AND OBJECT_NAME(sys.dm_exec_sql_text.objectid) = 'OBJECTNAME'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources