Can I see Historical Queries run on a SQL Server database?
Similar Grant Fritchey had the issue where he had closed SSMS and lost the query he had been working on...blogged about here: Oh **********!
EDIT
To make this a bit more detail of an answer, the referenced linked above Grant provides a query to simply go to the cache on the instance to pull out the query you had just executed (or atleast attempt to):
SELECT dest.text
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE deqs.last_execution_time > '5/19/2011 11:00'
AND dest.text LIKE 'WITH%';
A few more options that were noted in the comments of Grant's blog:
- Jamie Thomson points where SSMS actually has a "recovery" area under your Windows profile, similar to Word or Excel recovery.
- Another individual notes in comments about the SSMS Tools Pack, but this add-on is only free for SQL Server 2008. Starting with SQL Server 2012 it is paid only, but has many features you might find useful.
2005+, default trace to the rescue.
The default trace rolls over at 20mb but SQL retains the history of 5 traces. With access to the server you could retrieve the *.trc files from the MSSQL\Log directory. If you can't access the server, the following will give you the name of the current default trace file:
SELECT * FROM ::fn_trace_getinfo(default)
If the current file is for example E:\MSSQL.1\MSSQL\LOG\log_200.trc, the previous files should be log_199.trc, log_198.trc etc. Get the contents of the trace with:
SELECT * FROM fn_trace_gettable('E:\MSSQL.1\MSSQL\LOG\log_199.trc', default)
You might be able to retrieve info from cached query plans, check BOL for info on sys.dm_exec_query_stats, or run this from management studio connected to the same database:
SELECT d.plan_handle ,
d.sql_handle ,
e.text
FROM sys.dm_exec_query_stats d
CROSS APPLY sys.dm_exec_sql_text(d.plan_handle) AS e
Filter the output with
WHERE text like '%something%'
to narrow the results.