sql-server – 我可以从SQL Server默认获得哪些事件信息？

>谁最后执行了存储过程dbo.MyProcedure？
>谁更新了dbo.Employees表中的salary列？
>谁最后查询了Management Studio的dbo.Orders表？

但是,默认情况下SQL Server会暂时跟踪其他几个事件,并且可以原生回答有关的问题,例如：

> AdventureWorks数据库中最后一次自动增长是什么时候,需要多长时间？
>谁删除了dbo.EmployeeAuditData表,何时？
>今天发生了多少与内存相关的错误？

我如何获取此信息,以及它可以保留多长时间？

解决方法

您还可以从SQL Server错误日志,SQL Server代理日志,Windows事件日志以及SQL Server Audit,Management Data Warehouse,Event Notifications,DML Triggers,DDL Triggers,SCOM / System Center,您自己的服务器端跟踪或扩展事件等其他日志记录中查找某些信息.会话或第三方监控解决方案(如my employer,SQL Sentry所述).您也可以选择启用a so-called “Blackbox trace” to assist in troubleshooting.

但是对于这篇文章,我将把范围集中在通常最常用的东西上：默认跟踪,扩展事件会话和错误日志.

默认跟踪

默认跟踪通常在大多数系统上运行,除非您有disabled it using 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来了解更多详细信息,以查看哪些事件包含哪些数据,但我现在要跳过它,因为您可以在实际查询特定事件的跟踪数据时看到您拥有的内容.这些是我的系统上可用的事件(您应该在您的系统上运行查询以确保它们匹配)：

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

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

例子

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

Question: When was the last time an auto-grow happened in the AdventureWorks database,and how long did it take?

此查询将提取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;

Question: Who deleted the dbo.EmployeeAuditData table and when?

这将返回名为EmployeeAuditData的对象的任何DROP事件.如果要确保它只检测表的DROP事件,可以添加过滤器：ObjectType = 8277(full list is documented here).如果要将搜索空间限制为特定数据库,可以添加过滤器：DatabaseName = N’db_name’.

DECLARE @path NVARCHAR(260);

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

SELECT 
  LoginName,HostName,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对直接匹配没有用(因为该对象已被删除且不再存在).在这种情况下在输出中包含该列可能对于使用仍然存在的相同名称的表的任何副本进行交叉引用是有用的,但是如果系统处于这种混乱中(或者如果所有这些副本都已被删除)那里仍然可能不是一个可靠的方法来猜测谁删除了哪个表副本.

扩展事件

从Supporting SQL Server 2008: The system_health session (SQLCSS Blog)开始,以下是您可以从SQL Server 2008和2008 R2中的system_health会话中剔除的数据列表：

>遇到严重性为> = 20的错误的任何会话的sql_text和session_id
>遇到“内存”类型错误的任何会话的sql_text和session_id,如17803,701等(我们添加了这个,因为并非所有内存错误都是严重性> = 20)
>任何“非屈服”问题的记录(您有时在ERRORLOG中看到这些问题为Msg 17883)
>检测到的任何死锁
>对于等待锁定(或其他有趣资源)的任何会话的callstack,sql_text和session_id,用于> 15秒
>任何等待锁定的会话的callstack,sql_text和session_id为> 30秒
>任何会话的callstack,等待“外部”等待或“先发制人等待”的延长时间.

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

>遇到严重性> gt = 20的错误的任何会话的sql_text和session_id.
>遇到与内存相关的错误的任何会话的sql_text和session_id.错误包括17803,701,802,8645,8651,8657和8902.
>任何非屈服调度程序问题的记录. (它们在SQL Server错误日志中显示为错误17883.)
>检测到的任何死锁.
>对于等待锁定(或其他有趣资源)的任何会话的callstack,用于> 15秒
>任何已等待>锁定的会话的callstack,sql_text和session_id 30秒.
>用于抢占等待很长时间的任何会话的callstack,sql_text和session_id.持续时间因等待类型而异.抢先等待是SQL Server等待外部API调用的地方.
>用于CLR分配和虚拟分配失败的callstack和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关闭时已启动.

(该文档尚未更新,but I blogged about how I discover these and other changes.)

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

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

这些事件会话使用环形缓冲区目标来存储数据,因此 – 如缓冲池和计划缓存 – 旧事件将逐步淘汰,因此您不一定能够从所需的日期范围中提取事件.

例

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

How many memory-related errors have happened today?

以下是可以从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,8657,8902)
  AND ts >= CONVERT(DATE,CURRENT_TIMESTAMP)
  GROUP BY err;

DROP TABLE #blat;

(这个例子从Amit Banerjee’s introductory blog post on the system_health session开始宽容借用.)

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

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

错误日志

SQL Server默认保留当前加上6个最新的错误日志文件(但是you can change this).存储了大量信息,包括启动信息(正在使用的内核数量,内存中的锁定页面是否设置,身份验证模式等)以及错误和其他严重到足以记录的情况(并且未在其他地方捕获).最近的一个例子是有人在数据库脱机时寻找.您可以通过扫描每个最新的7个错误日志来确定文本设置数据库选项OFFLINE：

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

我介绍了其他一些细节in this recent answer,还有一些很好的背景信息at toadworld和in the official documentation.

默认情况下,错误日志会跟踪一组“错误” – 并且可以使重要信息更快地脱离 – 是每个成功的备份消息.您可以在enabling trace flag 3226之前阻止这些错误日志填满噪音.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!