Posso ver Consultas Históricas executadas em um banco de dados SQL Server?

Similarmente, Grant Fritchey teve o problema em que ele havia fechado o SSMS e perdido a consulta em que estava trabalhando... blogou aqui: Oh **********!

EDITAR

Para tornar isso um pouco mais detalhado de uma resposta, o link referenciado acima Grant fornece uma consulta para simplesmente ir ao cache na instância para retirar a consulta que você acabou de executar (ou pelo menos tentar):

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%';

Mais algumas opções que foram observadas nos comentários do blog de Grant:

• Jamie Thomson aponta onde o SSMS realmente tem uma área de "recuperação" em seu perfil do Windows, semelhante à recuperação do Word ou Excel.
• Outra nota individual nos comentários sobre o SSMS Tools Pack , mas este complemento é gratuito apenas para o SQL Server 2008. A partir do SQL Server 2012, ele é pago apenas, mas possui muitos recursos que podem ser úteis.

2005+, rastreamento padrão para o resgate.

O rastreamento padrão rola em 20 MB, mas o SQL retém o histórico de 5 rastreamentos. Com acesso ao servidor, você pode recuperar os arquivos *.trc do diretório MSSQL\Log. Se você não conseguir acessar o servidor, o seguinte fornecerá o nome do arquivo de rastreamento padrão atual:

SELECT * FROM ::fn_trace_getinfo(default) 

Se o arquivo atual for, por exemplo, E:\MSSQL.1\MSSQL\LOG\log_200.trc, os arquivos anteriores devem ser log_199.trc, log_198.trc etc. Obtenha o conteúdo do rastreamento com:

SELECT * FROM fn_trace_gettable('E:\MSSQL.1\MSSQL\LOG\log_199.trc', default)

Você pode recuperar informações de planos de consulta em cache, verificar BOL para obter informações em sys.dm_exec_query_stats ou executar isso no estúdio de gerenciamento conectado ao mesmo banco de dados:

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

Filtre a saída com

WHERE text like '%something%'

para estreitar os resultados.

Se o banco de dados estiver no modo de recuperação total, poderá haver uma chance de recuperar alguns dados e obter informações sobre o que foi feito lendo o log de transações.

Infelizmente, isso não é suportado por padrão, mas existem maneiras de fazer isso.

Você pode tentar usar ferramentas de terceiros, como ApexSQL Log ou SQL Log Rescue (gratuito, mas somente SQL 2000).

Outra opção é tentar usar funções não documentadas DBCC LOG ou fn_dblog. Isso é mais complexo, mas é gratuito.

O histórico de consultas pode ser visualizado usando as visualizações do sistema:

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

Por exemplo, usando a seguinte consulta:

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%'

As consultas em execução atuais podem ser vistas usando o seguinte script:

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])

Essa solicitação exibe todas as solicitações ativas e todas as solicitações que bloqueiam explicitamente as solicitações ativas.

Todos esses e outros scripts úteis são implementados como representações no banco de dados SRV , que é distribuído gratuitamente. Por exemplo, o primeiro script veio da view [inf].[vBigQuery] e o segundo veio da view [inf].[vRequests] .

Existem também várias soluções de terceiros para o histórico de consultas. Eu uso o Query Manager do Dbeaver : e o Query Execution History do SQL Tools , que está embutido no SSMS :

Se o seu banco de dados estiver definido para o modelo de recuperação completa, você poderá investigar os backups do log de transações. Veja fn_dump_dblogpara mais informações.