How to get a history of SQL scripts that have been run on a DB

Post a Comment

If you don’t have any sort of auditing mechanism enabled, its really hard to find get these information. But you may get that info from the cached plan. p

first step is to make sure that “optimize for ad hoc workloads” option is disabled on the sql server.

EXEC sp_CONFIGURE 'show advanced options',1
EXEC sp_CONFIGURE 'optimize for ad hoc workloads'

if the configured value is 1, then you may find those result using the following query which uses one of my favorite dmvs

SELECT  dest.text,deqs.last_execution_time
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

remember it is not guaranteed to return a results; the dmvs stores the data from the last sql server service start.

Related Posts

There is no other posts in this category.

Post a Comment

Subscribe Our Newsletter