sql-server – 我可以从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会话中剔除的数据列表：

（编辑：威海站长网）

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