AskOverflow.Dev

AskOverflow.Dev Logo AskOverflow.Dev Logo

AskOverflow.Dev Navigation

  • 主页
  • 系统&网络
  • Ubuntu
  • Unix
  • DBA
  • Computer
  • Coding
  • LangChain

Mobile menu

Close
  • 主页
  • 系统&网络
    • 最新
    • 热门
    • 标签
  • Ubuntu
    • 最新
    • 热门
    • 标签
  • Unix
    • 最新
    • 标签
  • DBA
    • 最新
    • 标签
  • Computer
    • 最新
    • 标签
  • Coding
    • 最新
    • 标签
主页 / dba / 问题 / 4043
Accepted
user87094
user87094
Asked: 2011-07-23 12:19:15 +0800 CST2011-07-23 12:19:15 +0800 CST 2011-07-23 12:19:15 +0800 CST

我可以查看在 SQL Server 数据库上运行的历史查询吗?

  • 772

有人远程在我们的 SQL Server 数据库上运行查询,他们的系统崩溃了。

他们没有该查询的备份,并希望查看服务器上运行的内容。

是否可以在某处的日志或历史记录中找到此查询?

sql-server logs
  • 6 6 个回答
  • 330430 Views

6 个回答

  • Voted
  1. Best Answer
    user507
    2011-07-23T13:53:29+08:002011-07-23T13:53:29+08:00

    类似的 Grant Fritchey 有一个问题,他关闭了 SSMS 并丢失了他一直在处理的查询……在这里写博客: 哦 **********!

    编辑

    为了让答案更详细一点,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%';
    

    格兰特博客的评论中提到的更多选项:

    • Jamie Thomson 指出 SSMS在您的 Windows 配置文件下实际上有一个“恢复”区域,类似于 Word 或 Excel 恢复。
    • 另一个关于 SSMS 工具包的评论中的个人注释,但此附加组件仅对 SQL Server 2008 是免费的。从 SQL Server 2012 开始,它只付费,但具有许多您可能会发现有用的功能。
    • 52
  2. Mark Storey-Smith
    2011-08-24T03:00:15+08:002011-08-24T03:00:15+08:00

    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)
    
    • 21
  3. SqlACID
    2011-07-23T13:01:03+08:002011-07-23T13:01:03+08:00

    您可能能够从缓存的查询计划中检索信息,检查 BOL 以获取有关 sys.dm_exec_query_stats 的信息,或者从连接到同一数据库的管理工作室运行它:

    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
    

    过滤输出

    WHERE text like '%something%'
    

    缩小结果。

    • 16
  4. Stanley Norman
    2013-06-10T21:04:57+08:002013-06-10T21:04:57+08:00

    如果数据库处于完全恢复模式,则可能有机会恢复一些数据并通过读取事务日志了解所做的事情。

    不幸的是,默认情况下不支持此功能,但有一些方法可以做到这一点。

    您可以尝试使用第三方工具,例如ApexSQL Log或SQL Log Rescue(免费但仅限 SQL 2000)。

    另一种选择是尝试使用未记录的函数 DBCC LOG 或 fn_dblog。这更复杂,但它是免费的。

    • 10
  5. Evgeniy Gribkov
    2020-06-14T03:20:23+08:002020-06-14T03:20:23+08:00

    可以使用系统视图查看查询历史记录:

    1. sys.dm_exec_query_stats
    2. sys.dm_exec_sql_text
    3. sys.dm_exec_query_plan

    例如,使用以下查询:

    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 的Query Manager 和 SQL Tools的Query Execution History,它嵌入在SSMS中: 在此处输入图像描述在此处输入图像描述

    • 2
  6. Matthias Elflein
    2016-07-22T00:21:50+08:002016-07-22T00:21:50+08:00

    如果您的数据库设置为完全恢复模式,您可以调查您的事务日志备份。有关fn_dump_dblog更多信息,请参阅。

    • 0

相关问题

  • SQL Server - 使用聚集索引时如何存储数据页

  • 我需要为每种类型的查询使用单独的索引,还是一个多列索引可以工作?

  • 什么时候应该使用唯一约束而不是唯一索引?

  • 死锁的主要原因是什么,可以预防吗?

  • 如何确定是否需要或需要索引

Sidebar

Stats

  • 问题 205573
  • 回答 270741
  • 最佳答案 135370
  • 用户 68524
  • 热门
  • 回答
  • Marko Smith

    你如何mysqldump特定的表?

    • 4 个回答
  • Marko Smith

    您如何显示在 Oracle 数据库上执行的 SQL?

    • 2 个回答
  • Marko Smith

    如何选择每组的第一行?

    • 6 个回答
  • Marko Smith

    使用 psql 列出数据库权限

    • 10 个回答
  • Marko Smith

    我可以查看在 SQL Server 数据库上运行的历史查询吗?

    • 6 个回答
  • Marko Smith

    如何在 PostgreSQL 中使用 currval() 来获取最后插入的 id?

    • 10 个回答
  • Marko Smith

    如何在 Mac OS X 上运行 psql?

    • 11 个回答
  • Marko Smith

    如何从 PostgreSQL 中的选择查询中将值插入表中?

    • 4 个回答
  • Marko Smith

    如何使用 psql 列出所有数据库和表?

    • 7 个回答
  • Marko Smith

    将数组参数传递给存储过程

    • 12 个回答
  • Martin Hope
    Manuel Leduc PostgreSQL 多列唯一约束和 NULL 值 2011-12-28 01:10:21 +0800 CST
  • Martin Hope
    markdorison 你如何mysqldump特定的表? 2011-12-17 12:39:37 +0800 CST
  • Martin Hope
    Stuart Blackler 什么时候应该将主键声明为非聚集的? 2011-11-11 13:31:59 +0800 CST
  • Martin Hope
    pedrosanta 使用 psql 列出数据库权限 2011-08-04 11:01:21 +0800 CST
  • Martin Hope
    Jonas 如何使用 psql 对 SQL 查询进行计时? 2011-06-04 02:22:54 +0800 CST
  • Martin Hope
    Jonas 如何从 PostgreSQL 中的选择查询中将值插入表中? 2011-05-28 00:33:05 +0800 CST
  • Martin Hope
    Jonas 如何使用 psql 列出所有数据库和表? 2011-02-18 00:45:49 +0800 CST
  • Martin Hope
    BrunoLM Guid vs INT - 哪个更好作为主键? 2011-01-05 23:46:34 +0800 CST
  • Martin Hope
    bernd_k 什么时候应该使用唯一约束而不是唯一索引? 2011-01-05 02:32:27 +0800 CST
  • Martin Hope
    Patrick 如何优化大型数据库的 mysqldump? 2011-01-04 13:13:48 +0800 CST

热门标签

sql-server mysql postgresql sql-server-2014 sql-server-2016 oracle sql-server-2008 database-design query-performance sql-server-2017

Explore

  • 主页
  • 问题
    • 最新
    • 热门
  • 标签
  • 帮助

Footer

AskOverflow.Dev

关于我们

  • 关于我们
  • 联系我们

Legal Stuff

  • Privacy Policy

Language

  • Pt
  • Server
  • Unix

© 2023 AskOverflow.DEV All Rights Reserve