Há muitas informações valiosas que o SQL Server rastreia para você por padrão. Desde o SQL Server 2005, existe um "rastreamento padrão" executado em segundo plano e, desde o SQL Server 2008, há uma sessão de eventos estendidos em execução automática, chamada system_health.

Você também pode encontrar certas informações do log de erros do SQL Server, log do SQL Server Agent, logs de eventos do Windows e logs adicionais de coisas como SQL Server Audit , Management Data Warehouse , Event Notifications , DML Triggers , DDL Triggers , SCOM / System Center , seus próprios rastreamentos do lado do servidor ou sessões de eventos estendidos ou soluções de monitoramento de terceiros (como aquelas feitas pelo SentryOne ). Você também pode, opcionalmente, ativar o chamado "rastreamento Blackbox" para ajudar na solução de problemas .

Mas, para esta postagem, vou focar o escopo em itens que geralmente são ativados em quase todos os lugares: o rastreamento padrão, as sessões de eventos estendidos e o log de erros.

Rastreamento Padrão

O rastreamento padrão geralmente está em execução na maioria dos sistemas, a menos que você o tenha desativado usandosp_configure - o que definitivamente sou um grande fã de fazer . Contanto que esteja habilitado, pode ser uma rica fonte de informações valiosas, embora em versões modernas do SQL Server, muito disso seja ruído e/ou já tenha sido capturado em outro lugar. A lista a seguir lista os eventos de rastreamento que são capturados:

DECLARE @TraceID INT;

SELECT @TraceID = id FROM sys.traces WHERE is_default = 1;

SELECT t.EventID, e.name as Event_Description
  FROM sys.fn_trace_geteventinfo(@TraceID) t
  JOIN sys.trace_events e ON t.eventID = e.trace_event_id
  GROUP BY t.EventID, e.name;

Você pode obter mais detalhes juntando-se a sys.trace_columnspara ver quais eventos vêm com quais dados, mas vou pular isso por enquanto, pois você pode ver o que tem quando realmente consulta os dados de rastreamento para eventos específicos. Estes são os eventos disponíveis no meu sistema (você deve executar a consulta no seu para ter certeza de que correspondem, embora ainda seja o mesmo conjunto de eventos por meio do SQL Server 2019 CTP 2.4):

EventID  Event_Description
-------  ----------------------------------------------
18       Audit Server Starts And Stops
20       Audit Login Failed
22       ErrorLog
46       Object:Created
47       Object:Deleted
55       Hash Warning
69       Sort Warnings
79       Missing Column Statistics
80       Missing Join Predicate
81       Server Memory Change
92       Data File Auto Grow
93       Log File Auto Grow
94       Data File Auto Shrink
95       Log File Auto Shrink
102      Audit Database Scope GDR Event
103      Audit Schema Object GDR Event
104      Audit Addlogin Event
105      Audit Login GDR Event
106      Audit Login Change Property Event
108      Audit Add Login to Server Role Event
109      Audit Add DB User Event
110      Audit Add Member to DB Role Event
111      Audit Add Role Event
115      Audit Backup/Restore Event
116      Audit DBCC Event
117      Audit Change Audit Event
152      Audit Change Database Owner
153      Audit Schema Object Take Ownership Event
155      FT:Crawl Started
156      FT:Crawl Stopped
164      Object:Altered
167      Database Mirroring State Change
175      Audit Server Alter Trace Event
218      Plan Guide Unsuccessful

Observe que o rastreamento padrão usa arquivos de substituição e, portanto, os dados disponíveis para você voltarão apenas até certo ponto - o intervalo de datas dos dados disponíveis depende de quantos dos eventos acima estão sendo capturados e com que frequência. Se você deseja garantir a manutenção de um histórico mais longo, pode configurar uma tarefa que arquive periodicamente os arquivos atualmente inativos associados ao rastreamento.

Exemplos

Na pergunta, fiz algumas perguntas que encontrei. Aqui estão exemplos de consultas para extrair essas informações específicas do rastreamento padrão.

Pergunta: Quando foi a última vez que ocorreu um crescimento automático no banco de dados AdventureWorks e quanto tempo demorou?

Esta consulta extrairá todos os eventos AutoGrow no banco de dados AdventureWorks, para arquivos de log e dados, que ainda estão nos arquivos de log de rastreamento padrão:

DECLARE @path NVARCHAR(260);

SELECT 
   @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT 
   DatabaseName,
   [FileName],
   SPID,
   Duration,
   StartTime,
   EndTime,
   FileType = CASE EventClass WHEN 92 THEN 'Data' ELSE 'Log' END
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass IN (92,93)
AND DatabaseName = N'AdventureWorks'
ORDER BY StartTime DESC;

Pergunta: Quem excluiu a tabela dbo.EmployeeAuditData e quando?

Isso retornará todos os DROPeventos para um objeto chamado EmployeeAuditData. Se você quiser ter certeza de que ele detecta apenas DROPeventos para tabelas, você pode adicionar um filtro: ObjectType = 8277(a lista completa está documentada aqui ). Se você deseja restringir o espaço de pesquisa a um banco de dados específico, pode adicionar um filtro: DatabaseName = N'db_name'.

DECLARE @path NVARCHAR(260);

SELECT 
   @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT 
  LoginName,
  HostName,
  StartTime,
  ObjectName,
  TextData
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 47    -- Object:Deleted
AND EventSubClass = 1
AND ObjectName = N'EmployeeAuditData'
ORDER BY StartTime DESC;

Há uma complicação aqui, e é um caso muito delicado, mas achei prudente mencionar de qualquer maneira. Se você usar vários esquemas e puder ter o mesmo nome de objeto em vários esquemas, não poderá dizer qual é (a menos que sua(s) contraparte(s) ainda exista(m). Há um caso externo em que UserA pode ter descartado SchemaB.Tablename enquanto UserB pode ter descartado SchemaA.Tablename. O rastreamento padrão não rastreia o esquema do objeto (nem captura TextDatapara este evento) e oObjectIDincluído no rastreamento não é útil para uma correspondência direta (porque o objeto foi descartado e não existe mais). Incluir essa coluna na saída neste caso pode ser útil para fazer referência cruzada com quaisquer cópias da tabela com o mesmo nome que ainda existam, mas se o sistema estiver tão desordenado (ou se todas essas cópias tiverem sido excluídas), haverá ainda pode não ser uma maneira confiável de adivinhar qual cópia da tabela foi descartada por quem.

Eventos Estendidos

A seguir está a lista de dados que você pode selecionar da system_healthsessão no SQL Server 2008 e 2008 R2 ( essa lista é mais completa em versões modernas ):

• O sql_text e o session_id para todas as sessões que encontrarem um erro com gravidade >=20
• O sql_text e o session_id para todas as sessões que encontrarem um tipo de erro de "memória", como 17803, 701 etc. (adicionamos isso porque nem todos os erros de memória são de gravidade >=20)
• Um registro de quaisquer problemas "não-cedentes" (às vezes você os vê no ERRORLOG como Msg 17883)
• Quaisquer impasses detectados
• A pilha de chamadas, sql_text e session_id para quaisquer sessões que esperaram em travas (ou outros recursos interessantes) por > 15 segundos
• A pilha de chamadas, sql_text e session_id para todas as sessões que aguardaram bloqueios por > 30 segundos
• A pilha de chamadas, sql_text e session_id para qualquer sessão que esperou por um longo período de tempo por esperas "externas" ou "esperas preventivas".

Em Use the system_health event session (MSDN) , a lista é um pouco expandida no SQL Server 2012 (e permanece a mesma para o SQL Server 2014):

• O sql_text e o session_id para todas as sessões que encontrarem um erro com gravidade >=20.
• O sql_text e o session_id para quaisquer sessões que encontrem um erro relacionado à memória. Os erros incluem 17803, 701, 802, 8645, 8651, 8657 e 8902.
• Um registro de quaisquer problemas do planejador não produtivo. (Eles aparecem no log de erros do SQL Server como erro 17883.)
• Quaisquer impasses detectados.
• A pilha de chamadas, sql_text e session_id para quaisquer sessões que esperaram em travas (ou outros recursos interessantes) por > 15 segundos.
• A pilha de chamadas, sql_text e session_id para todas as sessões que aguardaram bloqueios por > 30 segundos.
• A pilha de chamadas, sql_text e session_id para todas as sessões que esperaram por muito tempo por esperas preventivas. A duração varia de acordo com o tipo de espera. Uma espera preventiva é onde o SQL Server está aguardando chamadas de API externas.
• A pilha de chamadas e session_id para alocação CLR e falhas de alocação virtual.
• Os eventos ring_buffer para o mediador de memória, monitor do planejador, nó de memória OOM, segurança e conectividade.
• O componente do sistema resulta de sp_server_diagnostics.
• Integridade da instância coletada por scheduler_monitor_system_health_ring_buffer_recorded.
• Falhas de alocação de CLR.
• Erros de conectividade usando conectividade_ring_buffer_recorded.
• Erros de segurança usando security_error_ring_buffer_recorded.

No SQL Server 2016, mais dois eventos são capturados:

• Quando um processo é eliminado usando o KILLcomando.
• Quando o desligamento do SQL Server foi iniciado.

(A documentação ainda não foi atualizada, mas escrevi no blog sobre como descubro essas e outras alterações .)

Para obter a configuração mais enigmática aplicável à sua versão específica, você sempre pode executar a seguinte consulta diretamente, mas terá que interpretar os nomes e analisar os predicados para corresponder às listas de idiomas mais naturais acima:

SELECT e.package, e.event_id, e.name, e.predicate
  FROM sys.server_event_session_events AS e
  INNER JOIN sys.server_event_sessions AS s
  ON e.event_session_id = s.event_session_id
 WHERE s.name = N'system_health'
 ORDER BY e.package, e.name;

Se você estiver usando Grupos de Disponibilidade, também há duas novas sessões em execução: AlwaysOn_failovere AlwaysOn_health. Você pode ver os dados que eles coletam com a seguinte consulta:

SELECT s.name, e.package, e.event_id, e.name, e.predicate
  FROM sys.server_event_session_events AS e
  INNER JOIN sys.server_event_sessions AS s
  ON e.event_session_id = s.event_session_id
 WHERE s.name LIKE N'AlwaysOn[_]%'
 ORDER BY s.name, e.package, e.name;

Essas sessões de evento usam alvos de buffer de anel para armazenar os dados, portanto, como o buffer pool e o cache do plano, os eventos mais antigos serão eliminados gradualmente, portanto, você não poderá necessariamente extrair eventos do intervalo de datas desejado.

Exemplo

Na pergunta, coloquei esta pergunta fictícia:

Quantos erros relacionados à memória aconteceram hoje?

Aqui está um exemplo (e provavelmente não muito eficiente) de consulta que pode extrair essas informações da system_healthsessão:

;WITH src(x) AS
(
  SELECT y.query('.')
  FROM
  (
    SELECT x = CONVERT(XML, t.target_data)
      FROM sys.dm_xe_sessions AS s
      INNER JOIN sys.dm_xe_session_targets AS t
      ON s.[address] = t.event_session_address
      WHERE s.name = N'system_health'
  ) AS x
  CROSS APPLY x.x.nodes('/RingBufferTarget/event') AS y(y)
)
SELECT 
  x, ts = CONVERT(DATETIME, NULL), err = CONVERT(INT, NULL)
INTO #blat FROM src;

DELETE #blat WHERE x.value('(/event/@name)[1]', 'varchar(255)') <> 'error_reported';

UPDATE #blat SET ts = x.value('(/event/@timestamp)[1]', 'datetime');

UPDATE #blat SET err = x.value('(/event/data/value)[1]', 'int');

SELECT err, number_of_events = COUNT(*)
  FROM #blat
  WHERE err IN (17803, 701, 802, 8645, 8651, 8657, 8902)
  AND ts >= CONVERT(DATE, CURRENT_TIMESTAMP)
  GROUP BY err;

DROP TABLE #blat;

(Este exemplo toma emprestado vagamente da postagem introdutória do blog de Amit Banerjee na system_healthsessão .)

Para obter mais informações sobre eventos estendidos (incluindo muitos exemplos em que você pode consultar dados específicos), consulte esta série de blog de 31 partes de Jonathan Kehayias:

https://www.sqlskills.com/blogs/jonathan/an-xevent-a-day-31-days-of-extended-events/

Registro de erros

O SQL Server, por padrão, mantém os 6 arquivos de log de erros mais recentes (mas você pode alterar isso ). Muitas informações são armazenadas lá, incluindo informações de inicialização (quantos núcleos estão em uso, se as páginas de bloqueio na memória estão definidas, modo de autenticação etc.), bem como erros e outros cenários graves o suficiente para serem documentados (e não capturados em outro lugar). Um exemplo recente foi alguém procurando quando um banco de dados foi colocado offline. Você pode determinar isso examinando cada um dos 7 logs de erro mais recentes para o texto Setting database option OFFLINE:

EXEC sys.sp_readerrorlog 0,1,'Setting database option OFFLINE';
EXEC sys.sp_readerrorlog 1,1,'Setting database option OFFLINE';
EXEC sys.sp_readerrorlog 2,1,'Setting database option OFFLINE';
EXEC sys.sp_readerrorlog 3,1,'Setting database option OFFLINE';
EXEC sys.sp_readerrorlog 4,1,'Setting database option OFFLINE';
EXEC sys.sp_readerrorlog 5,1,'Setting database option OFFLINE';
EXEC sys.sp_readerrorlog 6,1,'Setting database option OFFLINE';

Cobri alguns outros detalhes nesta resposta recente , e também há algumas boas informações básicas no toadworld e também na documentação oficial .

Um grupo de "erros" que o log de erros rastreia por padrão - e pode fazer com que informações importantes caiam muito mais rapidamente - é cada mensagem de backup bem-sucedida. Você pode impedir que eles preencham o log de erros com ruído ativando o sinalizador de rastreamento 3226 .