We often come across the case when SSMS crashes and perhaps
your query was not saved. If you have the AutoSave feature enabled, you can
find a temporary file at this location:
c:\users\{username}\SQL Server Management Studio\Backup
Files\Solution1
If you do not have this feature, you can still recover the
script to the state when it was last executed by reading the information from
the dynamic management views and functions. This can allow us to capture the
SQL statements that were executed on the system for a particular period of
time. Please note that this only works while the query statistic cache is not
reset.
To recover your query, use the following query and enter a
search value and time frame.
USE MASTER
GO
SELECT query.last_execution_time AS [Date Time], execsql.text AS [Script]
FROM sys.dm_exec_query_stats
AS query
CROSS APPLY sys.dm_exec_sql_text(query.sql_handle) AS execsql
WHERE execsql.text like '%TAG-TO-SEARCH%'
and last_execution_time
> getdate()-1
ORDER BY query.last_execution_time DESC
In this query, I am only looking for queries for the last
day. You can remove that date constraint to expand the search. The TAG-TO-SEARCH value
should be replaced by a word that can help you get a closer match.
You can find detail information of dynamic views here
0 comments :
Post a Comment
What do you think?