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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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:
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