Log Shipping – NORECOVERY vs. STANDBY mode

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.