Saturday, February 11, 2012

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

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
RECONFIGURE
GO
EXEC sp_CONFIGURE 'optimize for ad hoc workloads'
RECONFIGURE
GO 


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.

0 comments:

Post a Comment