0

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? – TheGameiswar Jul 15 '16 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 '16 at 14:14
1

(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
  • DBCC FREESYSTEMCACHE('ALL')
  • 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;
| improve this answer | |
2

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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