默认情况下，SQL Server 会为您跟踪很多有价值的信息。自 SQL Server 2005 以来，有一个在后台运行的“默认跟踪”，自 SQL Server 2008 以来，有一个自动运行的扩展事件会话，称为system_health.

您还可以从 SQL Server 错误日志、SQL Server 代理日志、Windows 事件日志以及来自SQL Server 审计、管理数据仓库、事件通知、DML 触发器、DDL 触发器、SCOM / System Center等其他日志记录中找到某些信息，您自己的服务器端跟踪或扩展事件会话，或第三方监控解决方案（如SentryOne制作的那些）。您还可以选择启用所谓的“黑盒跟踪”来帮助进行故障排除。

但是对于这篇文章，我将把范围集中在通常在任何地方都启用的东西上：默认跟踪、扩展事件会话和错误日志。

默认跟踪

默认跟踪通常在大多数系统上运行，除非您使用禁用它sp_configure- 我绝对是这样做的忠实粉丝。只要启用它，它就可以成为有价值信息的丰富来源，尽管在现代版本的 SQL Server 中，其中大部分是噪音和/或已经在其他地方捕获。下面列出了捕获的跟踪事件：

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;

您可以通过加入来了解更多详细信息，sys.trace_columns以查看哪些事件与哪些数据一起出现，但我现在将跳过它，因为当您实际查询特定事件的跟踪数据时，您可以看到您拥有的内容。这些是我的系统上可用的事件（您应该在您的系统上运行查询以确保它们匹配，尽管这仍然是 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

请注意，默认跟踪使用翻转文件，因此您可用的数据只能追溯到到目前为止 - 可用数据的日期范围取决于捕获的上述事件的数量和频率。如果您想确保保留更长的历史记录，您可以设置一个定期归档与跟踪关联的当前非活动文件的作业。

例子

在这个问题中，我问了几个我发现的问题。以下是用于从默认跟踪中提取特定信息的示例查询。

问题：AdventureWorks 数据库中最后一次发生自动增长是什么时候，用了多长时间？

此查询将提取 AdventureWorks 数据库中的所有 AutoGrow 事件，包括日志和数据文件，它们仍位于默认跟踪日志文件中：

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;

问题：谁删除了 dbo.EmployeeAuditData 表，何时删除？

这将返回DROP名为 的对象的任何事件EmployeeAuditData。如果你想确保它只检测DROP表的事件，你可以添加一个过滤器：（ObjectType = 8277完整列表记录在这里）。如果要将搜索空间限制为特定数据库，可以添加过滤器：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;

这里有一个复杂的情况，这是非常极端的情况，但无论如何都要谨慎提及。如果您使用多个模式并且可能在多个模式中具有相同的对象名称，您将无法分辨这是哪一个（除非它的对应物仍然存在）。有一种外部情况，即 UserA 可能已删除 SchemaB.Tablename 而 UserB 可能已删除 SchemaA.Tablename。默认跟踪不跟踪对象的架构（也不捕获TextData此事件），并且ObjectID包含在跟踪中对直接匹配没有用（因为对象已被删除并且不再存在）。在这种情况下，在输出中包含该列可能有助于交叉引用仍然存在的同名表的任何副本，但是如果系统处于如此混乱的状态（或者如果所有此类副本都已被删除），则仍然可能不是猜测表的哪个副本被谁删除的可靠方法。

扩展活动

system_health以下是您可以从SQL Server 2008 和 2008 R2 中的会话中剔除的数据列表（该列表在现代版本中更完整）：

• 遇到严重性 >=20 错误的任何会话的 sql_text 和 session_id
• 遇到“内存”类型错误（例如 17803、701 等）的任何会话的 sql_text 和 session_id（我们添加了这一点，因为并非所有内存错误的严重性都 >=20）
• 任何“无法解决”问题的记录（您有时会在 ERRORLOG 中看到这些问题为 Msg 17883）
• 检测到的任何死锁
• 等待闩锁（或其他有趣资源）超过 15 秒的任何会话的调用堆栈、sql_text 和 session_id
• 等待锁超过 30 秒的任何会话的调用堆栈、sql_text 和 session_id
• 对于“外部”等待或“抢占式等待”已等待较长时间的任何会话的调用堆栈、sql_text 和 session_id。

从Use the system_health event session (MSDN) 开始，该列表在 SQL Server 2012 中有所扩展（对于 SQL Server 2014 保持不变）：

• 遇到严重性 >=20 的错误的任何会话的 sql_text 和 session_id。
• 遇到内存相关错误的任何会话的 sql_text 和 session_id。错误包括 17803、701、802、8645、8651、8657 和 8902。
• 任何非屈服调度程序问题的记录。（这些在 SQL Server 错误日志中显示为错误 17883。）
• 检测到的任何死锁。
• 等待锁存器（或其他有趣资源）超过 15 秒的任何会话的调用堆栈、sql_text 和 session_id。
• 等待锁定超过 30 秒的任何会话的调用堆栈、sql_text 和 session_id。
• 为抢占式等待而等待很长时间的任何会话的调用堆栈、sql_text 和 session_id。持续时间因等待类型而异。抢先式等待是 SQL Server 等待外部 API 调用的地方。
• CLR 分配和虚拟分配失败的调用堆栈和 session_id。
• 内存代理、调度程序监视器、内存节点 OOM、安全性和连接性的 ring_buffer 事件。
• 系统组件来自 sp_server_diagnostics。
• scheduler_monitor_system_health_ring_buffer_recorded 收集的实例运行状况。
• CLR 分配失败。
• 使用connectivity_ring_buffer_recorded 的连接错误。
• 使用 security_error_ring_buffer_recorded 的安全错误。

在 SQL Server 2016 中，还捕获了两个事件：

• 当使用KILL命令杀死进程时。
• 当 SQL Server 关闭已开始时。

（文档还没有更新，但我在博客上写了我是如何发现这些和其他变化的。）

要获得适用于您的特定版本的更神秘的配置，您始终可以直接运行以下查询，但您必须解释名称并解析谓词以匹配上面更自然的语言列表：

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;

如果您使用的是可用性组，您还会发现两个新会话正在运行：AlwaysOn_failover和AlwaysOn_health. 您可以通过以下查询查看他们收集的数据：

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;

这些事件会话使用环形缓冲区目标来存储数据，因此 - 就像缓冲池和计划缓存一样 - 较旧的事件将被逐步淘汰，因此您不一定能够从您想要的日期范围中提取事件。

例子

在这个问题中，我提出了这个虚构的问题：

今天发生了多少与内存相关的错误？

system_health这是一个可以从会话中提取此信息的示例（可能不是很有效）查询：

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

（这个例子大致借鉴了Amit Banerjee 关于会话的介绍性博客文章system_health。）

有关扩展事件的更多信息（包括您可以查询特定数据的许多示例），请参阅 Jonathan Kehayias 的这个由 31 部分组成的博客系列：

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

错误日志

默认情况下，SQL Server 保留当前加上 6 个最近的错误日志文件（但您可以更改此文件）。那里存储了大量信息，包括启动信息（正在使用多少个内核，是否设置了内存中的锁定页面，身份验证模式等）以及错误和其他严重到足以记录的场景（并且没有在其他地方捕获）。最近的一个例子是有人在数据库离线时进行查找。您可以通过扫描最近的 7 个错误日志中的每一个来确定这一点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';

我在这个最近的答案中介绍了一些其他细节，并且在 toadworld和官方文档中也有一些很好的背景信息。

错误日志默认跟踪的一组“错误”——并且可以使重要信息更快地从尾部消失——是每条成功的备份消息。您可以通过启用跟踪标志 3226来防止这些错误日志充满噪音。