when a stored procedure was last executed or who executed it?

最新推荐文章于 2024-02-04 22:04:59 发布

转载最新推荐文章于 2024-02-04 22:04:59 发布 · 163 阅读

文章标签：

#query #events #join #server #cache #up

SQL Daily Maintanence 专栏收录该内容

31 篇文章

订阅专栏

本文介绍了一种SQL查询方法，用于获取指定数据库中存储过程的最后执行时间。通过使用sys.dm_exec_procedure_stats动态管理视图，并结合sys.objects表进行内连接，可以有效地检索这些信息。然而，这种方法依赖于缓存状态，如果缓存刷新，则可能无法得到所需的数据。为了解决这个问题，可以设置轻量级服务器跟踪来持续捕获关键事件，从而长期监控存储过程的运行情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Query:

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) = 'databsename'

Limitation:

Querying the dynamic management view sys.dm_exec_query_stats will absolutely get you want you need. However it is dependent on the cache not having flushed the query . If the cache has flushed the query your options are limited to what you have in place in terms of monitoring. If you set up a lightweight server side trace that only captures a few events and columns, you can have that running 24/7 and be able to see whether or not a procedure has been run for a lot longer than the life of the cache.