SQL Server Audit – How to Tell if a Stored Procedure Has Executed Against a Particular Database

auditlogsql serversql-server-2008stored-procedures

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?

Best Answer

(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;
Related Question