Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Last Run Date on a Stored Procedure in SQL Server

Tags:

sql-server

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?

like image 720
Jon Edmiston Avatar asked Feb 27 '09 17:02

Jon Edmiston


People also ask

How can I tell when a stored procedure was last run in SQL Server?

The type_desc column includes the object type while the execution_count column shows the number of times the stored procedure has been executed since it was last compiled. This can be useful information when researching performance issues.

How do I view a stored procedure execution plan in SQL Server?

Following is the procedure to view the actual execution plan. Step 1 Connect to SQL Server instance. In this case, 'TESTINSTANCE' is the instance name. Step 2 − Click New Query option seen on the above screen and write the following query.


2 Answers

The below code should do the trick (>= 2008)

SELECT o.name,         ps.last_execution_time  FROM   sys.dm_exec_procedure_stats ps  INNER JOIN         sys.objects o         ON ps.object_id = o.object_id  WHERE  DB_NAME(ps.database_id) = ''  ORDER  BY         ps.last_execution_time DESC   

Edit 1 : Please take note of Jeff Modens advice below. If you find a procedure here, you can be sure that it is accurate. If you do not then you just don't know - you cannot conclude it is not running.

like image 143
Pixelated Avatar answered Sep 19 '22 11:09

Pixelated


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.

like image 28
gbn Avatar answered Sep 18 '22 11:09

gbn