SQL Server 中存储过程的上次运行日期 [英] Last Run Date on a Stored Procedure in SQL Server

问题描述

我们开始在我们的应用程序中获得很多存储过程.其中许多用于自定义报告，其中许多已不再使用.有谁知道我们可以在 SQL Server 2005 中的系统视图上运行的查询，它会告诉我们存储过程的最后执行日期?

We starting to get a lot of stored procedures in our application. Many of them are for custom reports many of which are no longer used. Does anyone know of a query we could run on the system views in SQL Server 2005 that would tell us the last date a stored procedure was executed?

推荐答案

简而言之，没有.

但是，您可以做一些不错"的事情.

However, there are "nice" things you can do.

1. 使用存储的过程名称运行分析器跟踪
2. 为每个 proc 添加一行(当然是创建一个表格)
   • "INSERT dbo.SPCall (What, When) VALUES (OBJECT_NAME(@@PROCID), GETDATE()"

你可以做一些有趣"的事情:

There are "fun" things you can do:

1. 删除它，看看是谁打电话
2. 删除权限，看看是谁打电话
3. 添加RAISERROR('Warning: pwn3d: call admin', 16, 1)，看看是谁打电话
4. 添加WAITFOR DELAY '00:01:00'，看看是谁打电话

1. Remove it, see who calls
2. Remove rights, see who calls
3. Add RAISERROR ('Warning: pwn3d: call admin', 16, 1), see who calls
4. Add WAITFOR DELAY '00:01:00', see who calls

你懂的.久经考验的看谁打电话"的 IT 支持方法.

You get the idea. The tried-and-tested "see who calls" method of IT support.

如果报告是 Reporting Services，那么您可以 挖掘 RS 数据库以运行报告，如果您可以将代码与报告数据集匹配.

If the reports are Reporting Services, then you can mine the RS database for the report runs if you can match code to report DataSet.

无论如何您都不能依赖 DMV，因为它们会在 SQL Server 重新启动时重置.查询缓存/锁是暂时的，不会持续任何时间.

You couldn't rely on DMVs anyway because they are reset om SQL Server restart. Query cache/locks are transient and don't persist for any length of time.

这篇关于SQL Server 中存储过程的上次运行日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！