As a result of a release a database is behaving "unusually". I suspect a stored procedure was missed during a recent release, however my DBA is telling me 100% it was executed.

Is there any way to definitively prove it was or wasn't?

  • are you talking about release scripts or stored procedures inside database? Jul 15, 2016 at 13:40
  • Existing sp's within the db, but needed to be run to update some settings after a release (and I don't think was!)
    – BlueChippy
    Jul 15, 2016 at 14:14

2 Answers 2


(emphasis added)

Is there any way to definitively prove it was/wasn't

No, there is no way to prove in any definite way that it has been executed, outside of coding the stored procedure to do something like insert a record into a log table showing that it had been executed. In your particular case, this would also require a time machine in order to put that into place, and I do not believe that those are even possible to create in the first place :-(.

There are DMVs which can track executions, but they get cleared out for various reasons:

  • service restart
  • memory pressure
  • maybe others

Of course, if you do see it in any DMV, that does prove that it was executed, but you better take a screen shot because it might be gone the next time you run that query to show someone that it is there ;-). However, not finding it in a DMV does not prove that it was never executed.

So, along with the query suggested by @TheGameiswar, you could also try the following:

SELECT qplan.[objectid], qplan.[dbid], OBJECT_NAME(qplan.[objectid], qplan.[dbid])
FROM sys.dm_exec_cached_plans cplan
CROSS APPLY sys.dm_exec_query_plan(cplan.[plan_handle]) qplan
WHERE qplan.[objectid] IS NOT NULL;

Starting from SQL Server 2008,use can sys.dm_exec_procedure_stats which gives you the status on when was the stored procedure last executed

select db_name(database_id) as dbname,last_execution_time 
from sys.dm_exec_procedure_stats

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.