Last Run Date on a Stored Procedure in MS SQL

Question

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?

Answer 1

In a nutshell, no.

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

1. Run a profiler trace with, say, the stored proc name
2. Add a line each proc (create a tabel of course)
   • "INSERT dbo.SPCall (What, When) VALUES (OBJECT_NAME(@@PROCID), GETDATE()"
3. Extend 2 with duration too

There are "fun" things you can do:

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

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

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.

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.

Answer 2

I don't know if anyone is still keeping an eye on this question, but I like the idea of using the insert statement within the stored procedures to track the run dates but have a question.

If I add an insert statement to each stored procedure that we have (hundreds of SPs), am I taking the risk of having the table lock due to insert statements possibling being triggered at the same time? I need to make sure that if I modify all of our SPs that they won't be killed by the insert statement if another SP is also trying to insert.

Thanks! Katie