Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

logging - How to see query history in SQL Server Management Studio

Is the query history stored in some log files? If yes, can you tell me how to find their location? If not, can you give me any advice on how to see it?

Question&Answers:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

[Since this question will likely be closed as a duplicate.]

If SQL Server hasn't been restarted (and the plan hasn't been evicted, etc.), you may be able to find the query in the plan cache.

SELECT t.[text]
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE t.[text] LIKE N'%something unique about your query%';

If you lost the file because Management Studio crashed, you might be able to find recovery files here:

C:Users<you>DocumentsSQL Server Management StudioBackup Files

Otherwise you'll need to use something else going forward to help you save your query history, like SSMS Tools Pack as mentioned in Ed Harper's answer - though it isn't free in SQL Server 2012+. Or you can set up some lightweight tracing filtered on your login or host name (but please use a server-side trace, not Profiler, for this).


As @Nenad-Zivkovic commented, it might be helpful to join on sys.dm_exec_query_stats and order by last_execution_time:

SELECT t.[text], s.last_execution_time
FROM sys.dm_exec_cached_plans AS p
INNER JOIN sys.dm_exec_query_stats AS s
   ON p.plan_handle = s.plan_handle
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE t.[text] LIKE N'%something unique about your query%'
ORDER BY s.last_execution_time DESC;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...