默认情况下，我可以从SQL Server获取哪些事件信息？

#1 楼

默认情况下，SQL Server会为您跟踪很多有价值的信息。由于SQL Server 2005一直在后台运行“默认跟踪”，并且由于SQL Server 2008一直在自动运行扩展事件会话，称为system_health。
您还可以从SQL Server中找到某些信息。错误日志，SQL Server代理日志，Windows事件日志以及来自SQL Server审核，管理数据仓库，事件通知，DML触发器，DDL触发器，SCOM /系统中心，您自己的服务器端跟踪或扩展事件之类的其他日志会话或第三方监视解决方案（例如SentryOne制造的解决方案）。您还可以选择启用所谓的“黑匣子跟踪”以帮助进行故障排除。
但是，在本文中，我将把重点放在通常在所有地方都启用的东西上：默认跟踪，扩展事件会话以及错误日志。
默认跟踪
默认跟踪通常在大多数系统上运行，除非您使用sp_configure禁用了它。只要启用它，它就可以成为有价值信息的丰富来源。下面列出了捕获的跟踪事件：

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对于直接匹配没有用（因为该对象已删除并且不再存在）。在这种情况下，在输出中包括该列可能有助于交叉引用仍存在的具有相同名称的表的任何副本，但是如果系统处于如此混乱的状态（或者如果所有此类副本都已删除）仍然可能不是可靠的方法来猜测由谁删除了表的哪个副本。
扩展事件
以下是您可以从SQL Server 2008和2008中的system_health会话中剔除的数据列表。 R2（该列表在现代版本中更为完整）：

遇到严重性> = 20的错误的任何会话的sql_text和session_id
遇到严重性> 2的任何会话的sql_text和session_id “内存”类型的错误，例如17803、701等（我们添加了此内容，因为并非所有内存错误的严重性均大于等于20）
任何“非屈服”问题的记录（您有时会在ERRORLOG（MSG 17883）
检测到任何死锁
任何会话的调用堆栈，sql_text和session_id等待锁存器（或其他有趣资源）的时间超过15秒的s
对于等待锁的时间超过30秒的任何会话的调用堆栈，sql_text和session_id对于已等待较长时间的“外部”等待或“抢先等待”的会话。

从“使用system_health事件会话（MSDN）”中，该列表在SQL Server 2012中有所扩展（对于SQL Server 2014则保持不变）：严重性> = 20。
遇到内存相关错误的任何会话的sql_text和session_id。错误包括17803、701、802、8645、8651、8657和8902。
任何非屈服调度程序问题的记录。 （这些错误在SQL Server错误日志中显示为错误17883。）
检测到任何死锁。
对于等待闩锁（或其他有趣资源）的任何会话，其调用堆栈，sql_text和session_id > 15秒。
等待锁定超过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的连接性错误。

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

使用KILL命令杀死进程时。 br />
（文档尚未更新，但是我在博客中介绍了如何发现这些以及其他更改。）
要获得适用于您特定版本的更神秘的配置，您始终可以直接运行以下查询，但是您必须解释名称并解析谓词以匹配上面更自然的语言列表：

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，可以防止这些错误使错误日志充满噪声。