他们没有该查询的备份，想查看服务器上正在运行的内容。
是否可以在日志或历史记录中找到该查询？
#1 楼
类似的Grant Fritchey遇到的问题是，他关闭了SSMS，并丢失了他一直在处理的查询...在此处发布了以下博客：哦**********！
EDIT
为使答案更详细，Grant上方的引用链接提供了一个查询，可简单地转到实例上的缓存以提取您刚刚执行的查询（或至少查询）尝试）：
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%';
Grant博客的评论中提到了一些其他选项：
Jamie Thomson指出，SSMS在Windows配置文件下实际上有一个“恢复”区域，类似于Word或Excel恢复。
关于SSMS工具包的注释中还有其他一些注释，但是此附加组件是仅对SQL Server 2008免费。从SQL Server 2012开始，它仅是付费的，但具有许多可能会有用的功能。
评论
那是一篇不错的文章！谢谢！在Grant的文章之后，在SQL Server Management Studio中恢复备份的查询文件可能会有所帮助。
–玛丽安
2011年7月22日在21:59
#2 楼
2005+，默认跟踪可用于救援。默认跟踪在20mb处翻转，但SQL保留5条跟踪的历史记录。通过访问服务器，您可以从MSSQL \ Log目录中检索* .trc文件。如果无法访问服务器，则以下内容将为您提供当前默认跟踪文件的名称：
SELECT * FROM ::fn_trace_getinfo(default)
如果当前文件例如是E：\ MSSQL.1 \ MSSQL \ LOG \ log_200.trc，以前的文件应该是log_199.trc，log_198.trc等。使用以下方法获取跟踪的内容：
SELECT * FROM fn_trace_gettable('E:\MSSQL.1\MSSQL\LOG\log_199.trc', default)
#3 楼
您可能能够从缓存的查询计划中检索信息，检查BOL上有关sys.dm_exec_query_stats的信息，或者从连接到相同数据库的管理Studio中运行此信息：用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
过滤输出以缩小结果。
#4 楼
如果数据库处于完全恢复模式，则可能有机会恢复一些数据并通过读取事务日志来了解已完成的操作。这个。您可以尝试使用第三方工具，例如ApexSQL Log或SQL Log Rescue（免费，但仅限SQL 2000）。
另一种选择是尝试使用未记录的函数DBCC LOG或fn_dblog。这比较复杂，但是它是免费的。
#5 楼
可使用系统视图查看查询历史记录：例如使用以下查询：
select top(100)
creation_time,
last_execution_time,
execution_count,
total_worker_time/1000 as CPU,
convert(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime],
qs.total_elapsed_time/1000 as TotDuration,
convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur],
total_logical_reads as [Reads],
total_logical_writes as [Writes],
total_logical_reads+total_logical_writes as [AggIO],
convert(money, (total_logical_reads+total_logical_writes)/(execution_count + 0.0)) as [AvgIO],
[sql_handle],
plan_handle,
statement_start_offset,
statement_end_offset,
plan_generation_num,
total_physical_reads,
convert(money, total_physical_reads/(execution_count + 0.0)) as [AvgIOPhysicalReads],
convert(money, total_logical_reads/(execution_count + 0.0)) as [AvgIOLogicalReads],
convert(money, total_logical_writes/(execution_count + 0.0)) as [AvgIOLogicalWrites],
query_hash,
query_plan_hash,
total_rows,
convert(money, total_rows/(execution_count + 0.0)) as [AvgRows],
total_dop,
convert(money, total_dop/(execution_count + 0.0)) as [AvgDop],
total_grant_kb,
convert(money, total_grant_kb/(execution_count + 0.0)) as [AvgGrantKb],
total_used_grant_kb,
convert(money, total_used_grant_kb/(execution_count + 0.0)) as [AvgUsedGrantKb],
total_ideal_grant_kb,
convert(money, total_ideal_grant_kb/(execution_count + 0.0)) as [AvgIdealGrantKb],
total_reserved_threads,
convert(money, total_reserved_threads/(execution_count + 0.0)) as [AvgReservedThreads],
total_used_threads,
convert(money, total_used_threads/(execution_count + 0.0)) as [AvgUsedThreads],
case
when sql_handle IS NULL then ' '
else(substring(st.text,(qs.statement_start_offset+2)/2,(
case
when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2
else qs.statement_end_offset
end - qs.statement_start_offset)/2 ))
end as query_text,
db_name(st.dbid) as database_name,
object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as [object_name],
sp.[query_plan]
from sys.dm_exec_query_stats as qs with(readuncommitted)
cross apply sys.dm_exec_sql_text(qs.[sql_handle]) as st
cross apply sys.dm_exec_query_plan(qs.[plan_handle]) as sp
WHERE st.[text] LIKE '%query%'
可以使用以下脚本查看当前运行的查询：
select ES.[session_id]
,ER.[blocking_session_id]
,ER.[request_id]
,ER.[start_time]
,DateDiff(second, ER.[start_time], GetDate()) as [date_diffSec]
, COALESCE(
CAST(NULLIF(ER.[total_elapsed_time] / 1000, 0) as BIGINT)
,CASE WHEN (ES.[status] <> 'running' and isnull(ER.[status], '') <> 'running')
THEN DATEDIFF(ss,0,getdate() - nullif(ES.[last_request_end_time], '1900-01-01T00:00:00.000'))
END
) as [total_time, sec]
, CAST(NULLIF((CAST(ER.[total_elapsed_time] as BIGINT) - CAST(ER.[wait_time] AS BIGINT)) / 1000, 0 ) as bigint) as [work_time, sec]
, CASE WHEN (ER.[status] <> 'running' AND ISNULL(ER.[status],'') <> 'running')
THEN DATEDIFF(ss,0,getdate() - nullif(ES.[last_request_end_time], '1900-01-01T00:00:00.000'))
END as [sleep_time, sec] --Время сна в сек
, NULLIF( CAST((ER.[logical_reads] + ER.[writes]) * 8 / 1024 as numeric(38,2)), 0) as [IO, MB]
, CASE ER.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommited'
WHEN 2 THEN 'ReadCommited'
WHEN 3 THEN 'Repetable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END as [transaction_isolation_level_desc]
,ER.[status]
,ES.[status] as [status_session]
,ER.[command]
,ER.[percent_complete]
,DB_Name(coalesce(ER.[database_id], ES.[database_id])) as [DBName]
, SUBSTRING(
(select top(1) [text] from sys.dm_exec_sql_text(ER.[sql_handle]))
, ER.[statement_start_offset]/2+1
, (
CASE WHEN ((ER.[statement_start_offset]<0) OR (ER.[statement_end_offset]<0))
THEN DATALENGTH ((select top(1) [text] from sys.dm_exec_sql_text(ER.[sql_handle])))
ELSE ER.[statement_end_offset]
END
- ER.[statement_start_offset]
)/2 +1
) as [CURRENT_REQUEST]
,(select top(1) [text] from sys.dm_exec_sql_text(ER.[sql_handle])) as [TSQL]
,(select top(1) [objectid] from sys.dm_exec_sql_text(ER.[sql_handle])) as [objectid]
,(select top(1) [query_plan] from sys.dm_exec_query_plan(ER.[plan_handle])) as [QueryPlan]
,NULL as [event_info]--(select top(1) [event_info] from sys.dm_exec_input_buffer(ES.[session_id], ER.[request_id])) as [event_info]
,ER.[wait_type]
,ES.[login_time]
,ES.[host_name]
,ES.[program_name]
,cast(ER.[wait_time]/1000 as decimal(18,3)) as [wait_timeSec]
,ER.[wait_time]
,ER.[last_wait_type]
,ER.[wait_resource]
,ER.[open_transaction_count]
,ER.[open_resultset_count]
,ER.[transaction_id]
,ER.[context_info]
,ER.[estimated_completion_time]
,ER.[cpu_time]
,ER.[total_elapsed_time]
,ER.[scheduler_id]
,ER.[task_address]
,ER.[reads]
,ER.[writes]
,ER.[logical_reads]
,ER.[text_size]
,ER.[language]
,ER.[date_format]
,ER.[date_first]
,ER.[quoted_identifier]
,ER.[arithabort]
,ER.[ansi_null_dflt_on]
,ER.[ansi_defaults]
,ER.[ansi_warnings]
,ER.[ansi_padding]
,ER.[ansi_nulls]
,ER.[concat_null_yields_null]
,ER.[transaction_isolation_level]
,ER.[lock_timeout]
,ER.[deadlock_priority]
,ER.[row_count]
,ER.[prev_error]
,ER.[nest_level]
,ER.[granted_query_memory]
,ER.[executing_managed_code]
,ER.[group_id]
,ER.[query_hash]
,ER.[query_plan_hash]
,EC.[most_recent_session_id]
,EC.[connect_time]
,EC.[net_transport]
,EC.[protocol_type]
,EC.[protocol_version]
,EC.[endpoint_id]
,EC.[encrypt_option]
,EC.[auth_scheme]
,EC.[node_affinity]
,EC.[num_reads]
,EC.[num_writes]
,EC.[last_read]
,EC.[last_write]
,EC.[net_packet_size]
,EC.[client_net_address]
,EC.[client_tcp_port]
,EC.[local_net_address]
,EC.[local_tcp_port]
,EC.[parent_connection_id]
,EC.[most_recent_sql_handle]
,ES.[host_process_id]
,ES.[client_version]
,ES.[client_interface_name]
,ES.[security_id]
,ES.[login_name]
,ES.[nt_domain]
,ES.[nt_user_name]
,ES.[memory_usage]
,ES.[total_scheduled_time]
,ES.[last_request_start_time]
,ES.[last_request_end_time]
,ES.[is_user_process]
,ES.[original_security_id]
,ES.[original_login_name]
,ES.[last_successful_logon]
,ES.[last_unsuccessful_logon]
,ES.[unsuccessful_logons]
,ES.[authenticating_database_id]
,ER.[sql_handle]
,ER.[statement_start_offset]
,ER.[statement_end_offset]
,ER.[plan_handle]
,NULL as [dop]--ER.[dop]
,coalesce(ER.[database_id], ES.[database_id]) as [database_id]
,ER.[user_id]
,ER.[connection_id]
from sys.dm_exec_requests ER with(readuncommitted)
right join sys.dm_exec_sessions ES with(readuncommitted)
on ES.session_id = ER.session_id
left join sys.dm_exec_connections EC with(readuncommitted)
on EC.session_id = ES.session_id
where ER.[status] in ('suspended', 'running', 'runnable')
or exists (select top(1) 1 from sys.dm_exec_requests as ER0 where ER0.[blocking_session_id]=ES.[session_id])
该请求显示所有活动请求以及所有显式阻止活动请求的请求。
所有这些和其他有用的脚本均作为SRV数据库中的表示形式实现，可以自由分发。 ，第一个脚本来自视图[inf]。[vBigQuery]，第二个脚本来自视图[inf]。[vRequests]。
查询历史记录也有各种第三方解决方案。
我使用Dbeaver的查询管理器：
和嵌入在SSMS中的SQL工具的查询执行历史记录：
#6 楼
如果数据库设置为完全恢复模型，则可以调查事务日志备份。有关详细信息，请参阅
fn_dump_dblog。
评论
对于将来的设计，您可能需要考虑添加触发器和审计/历史记录表。这样便可以利用上次更新的时间/用户。