当前位置：Gxlcms > 数据库问题 > SQL Server 默认跟踪(Trace)捕获事件详解

SQL Server 默认跟踪(Trace)捕获事件详解

时间：2021-07-01 10:21:17 帮助过：17人阅读

returns full list of events SELECT * FROM sys.trace_events --returns a full list of categories SELECT * FROM sys.trace_categories --returns a full list of subclass values SELECT * FROM sys.trace_subclass_values

 

Database 事件
让我们先从第一个事件开始：Database事件。它的子类事件包括数据文件和日志文件的自动增长和收缩，以及数据库镜像状态的改变。监控文件的增长和收缩是非常重要的；它可能隐射出性能问题。每次文件的增长和收缩，SQL Server将会挂起等待磁盘系统去让文件再次可用。在这里，挂起halt，是指直到进程完成前是没有事务处理的。

 

有如下数据库事件被监控：

? Data file auto grow

? Data file auto shrink

? Database mirroring status change

? Log file auto grow

? Log file auto shrink

 

如下脚本可以列出数据文件的增长和收缩：

  1. <span style="color: #0000ff;">SELECT</span>  TE.name <span style="color: #0000ff;">AS</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">EventName</span><span style="color: #ff0000;">]</span><span style="color: #000000;"> ,
  2. T.DatabaseName ,
  3. t.DatabaseID ,
  4. t.NTDomainName ,
  5. t.ApplicationName ,
  6. t.LoginName ,
  7. t.SPID ,
  8. t.Duration ,
  9. t.StartTime ,
  10. t.EndTime
  11. </span><span style="color: #0000ff;">FROM</span> sys.fn_trace_gettable(<span style="color: #ff00ff;">CONVERT</span>(<span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">150</span>), ( <span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">TOP</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">
  12. f.</span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">value</span><span style="color: #ff0000;">]</span>
  13. <span style="color: #0000ff;">FROM</span> sys.fn_trace_getinfo(<span style="color: #0000ff;">NULL</span><span style="color: #000000;">) f
  14. </span><span style="color: #0000ff;">WHERE</span> f.property <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;">
  15. )), </span><span style="color: #0000ff;">DEFAULT</span><span style="color: #000000;">) T
  16. </span><span style="color: #808080;">JOIN</span> sys.trace_events TE <span style="color: #0000ff;">ON</span> T.EventClass <span style="color: #808080;">=</span><span style="color: #000000;"> TE.trace_event_id
  17. </span><span style="color: #0000ff;">WHERE</span> te.name <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Data File Auto Grow</span><span style="color: #ff0000;">‘</span>
  18. <span style="color: #808080;">OR</span> te.name <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Data File Auto Shrink</span><span style="color: #ff0000;">‘</span>
  19. <span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span><span style="color: #000000;"> t.StartTime ;
  20.  </span>

 

该脚本的输出不会告诉你为什么增长，但是会告诉你增长花了多长事件。（关于Duration列的单位，会根据SQL Server的版本，可能是milliseconds或者microseconds）

而且，我推荐扩展这个查询语句，搜索增长时间大于1秒的数据库。这只是一个引导，可以根据自己的需求去扩展。

 

这里是另一个返回日志增长和收缩的查询语句：

  1. <span style="color: #0000ff;">SELECT</span> TE.name <span style="color: #0000ff;">AS</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">EventName</span><span style="color: #ff0000;">]</span><span style="color: #000000;"> ,
  2. T.DatabaseName ,
  3. t.DatabaseID ,
  4. t.NTDomainName ,
  5. t.ApplicationName ,
  6. t.LoginName ,
  7. t.SPID ,
  8. t.Duration ,
  9. t.StartTime ,
  10. t.EndTime
  11. </span><span style="color: #0000ff;">FROM</span> sys.fn_trace_gettable(<span style="color: #ff00ff;">CONVERT</span>(<span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">150</span>), ( <span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">TOP</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">
  12. f.</span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">value</span><span style="color: #ff0000;">]</span>
  13. <span style="color: #0000ff;">FROM</span> sys.fn_trace_getinfo(<span style="color: #0000ff;">NULL</span><span style="color: #000000;">) f
  14. </span><span style="color: #0000ff;">WHERE</span> f.property <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;">
  15. )), </span><span style="color: #0000ff;">DEFAULT</span><span style="color: #000000;">) T
  16. </span><span style="color: #808080;">JOIN</span> sys.trace_events TE <span style="color: #0000ff;">ON</span> T.EventClass <span style="color: #808080;">=</span><span style="color: #000000;"> TE.trace_event_id
  17. </span><span style="color: #0000ff;">WHERE</span> te.name <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Log File Auto Grow</span><span style="color: #ff0000;">‘</span>
  18. <span style="color: #808080;">OR</span> te.name <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Log File Auto Shrink</span><span style="color: #ff0000;">‘</span>
  19. <span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span> t.StartTime ;

 

而且要记住，这个查询不会告诉你，是否你的初级DBA在收缩数据和日志文件。在默认跟踪，我们只能找到自动增长和自动收缩的事件，并且不是通过ALTER DATABASE语句触发的。

 

Errors and Warnings事件
现在，让我们来看下一个事件：Errors and Warnings事件。可以看到，也有丰富的子事件。

当写事件到SQL Server事件日志中时，Errorlog子事件触发。当哈希匹配操作或排序操作溢出到磁盘（因为磁盘子系统是最慢的，我们的查询将变得更慢），Hash warning和Sort warnings事件会发生。只有当“Auto create statistics”选项被设置为off时，Missing column statistics事件才会发生。在这里，SQL Server说明了它可能已经关闭了一个不好的执行计划。当两个表没有连接谓词，且当所有表不止一行时，Missing join predicate事件发生。可以得到慢查询或者不可预期的结果。

 

Errors and Warnings的子类包括：

? Errorlog

? Hash warning

? Missing Column Statistics

? Missing Join Predicate

? Sort Warning

 

输出错误的脚本：

  1. <span style="color: #0000ff;">SELECT</span> TE.name <span style="color: #0000ff;">AS</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">EventName</span><span style="color: #ff0000;">]</span><span style="color: #000000;"> ,
  2. T.DatabaseName ,
  3. t.DatabaseID ,
  4. t.NTDomainName ,
  5. t.ApplicationName ,
  6. t.LoginName ,
  7. t.SPID ,
  8. t.StartTime ,
  9. t.TextData ,
  10. t.Severity ,
  11. t.Error
  12. </span><span style="color: #0000ff;">FROM</span> sys.fn_trace_gettable(<span style="color: #ff00ff;">CONVERT</span>(<span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">150</span>), ( <span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">TOP</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">
  13. f.</span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">value</span><span style="color: #ff0000;">]</span>
  14. <span style="color: #0000ff;">FROM</span> sys.fn_trace_getinfo(<span style="color: #0000ff;">NULL</span><span style="color: #000000;">) f
  15. </span><span style="color: #0000ff;">WHERE</span> f.property <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;">
  16. )), </span><span style="color: #0000ff;">DEFAULT</span><span style="color: #000000;">) T
  17. </span><span style="color: #808080;">JOIN</span> sys.trace_events TE <span style="color: #0000ff;">ON</span> T.EventClass <span style="color: #808080;">=</span><span style="color: #000000;"> TE.trace_event_id
  18. </span><span style="color: #0000ff;">WHERE</span> te.name <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">ErrorLog</span><span style="color: #ff0000;">‘</span>


注意，这个脚本没有EndTime或者Duration列。

 

另一个脚本输出排序和哈希警告：

  1. <span style="color: #0000ff;">SELECT</span> TE.name <span style="color: #0000ff;">AS</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">EventName</span><span style="color: #ff0000;">]</span><span style="color: #000000;"> ,
  2. v.subclass_name ,
  3. T.DatabaseName ,
  4. t.DatabaseID ,
  5. t.NTDomainName ,
  6. t.ApplicationName ,
  7. t.LoginName ,
  8. t.SPID ,
  9. t.StartTime
  10. </span><span style="color: #0000ff;">FROM</span> sys.fn_trace_gettable(<span style="color: #ff00ff;">CONVERT</span>(<span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">150</span>), ( <span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">TOP</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">
  11. f.</span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">value</span><span style="color: #ff0000;">]</span>
  12. <span style="color: #0000ff;">FROM</span> sys.fn_trace_getinfo(<span style="color: #0000ff;">NULL</span><span style="color: #000000;">) f
  13. </span><span style="color: #0000ff;">WHERE</span> f.property <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;">
  14. )), </span><span style="color: #0000ff;">DEFAULT</span><span style="color: #000000;">) T
  15. </span><span style="color: #808080;">JOIN</span> sys.trace_events TE <span style="color: #0000ff;">ON</span> T.EventClass <span style="color: #808080;">=</span><span style="color: #000000;"> TE.trace_event_id
  16. </span><span style="color: #808080;">JOIN</span> sys.trace_subclass_values v <span style="color: #0000ff;">ON</span> v.trace_event_id <span style="color: #808080;">=</span><span style="color: #000000;"> TE.trace_event_id
  17. </span><span style="color: #808080;">AND</span> v.subclass_value <span style="color: #808080;">=</span><span style="color: #000000;"> t.EventSubClass
  18. </span><span style="color: #0000ff;">WHERE</span> te.name <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Hash Warning</span><span style="color: #ff0000;">‘</span>
  19. <span style="color: #808080;">OR</span> te.name <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Sort Warnings</span><span style="color: #ff0000;">‘</span>

最后，脚本会输出失效的统计信息和谓词连接：

  1. <span style="color: #0000ff;">SELECT</span> TE.name <span style="color: #0000ff;">AS</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">EventName</span><span style="color: #ff0000;">]</span><span style="color: #000000;"> ,
  2. T.DatabaseName ,
  3. t.DatabaseID ,
  4. t.NTDomainName ,
  5. t.ApplicationName ,
  6. t.LoginName ,
  7. t.SPID ,
  8. t.StartTime
  9. </span><span style="color: #0000ff;">FROM</span> sys.fn_trace_gettable(<span style="color: #ff00ff;">CONVERT</span>(<span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">150</span>), ( <span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">TOP</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">
  10. f.</span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">value</span><span style="color: #ff0000;">]</span>
  11. <span style="color: #0000ff;">FROM</span> sys.fn_trace_getinfo(<span style="color: #0000ff;">NULL</span><span style="color: #000000;">) f
  12. </span><span style="color: #0000ff;">WHERE</span> f.property <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;">
  13. )), </span><span style="color: #0000ff;">DEFAULT</span><span style="color: #000000;">) T
  14. </span><span style="color: #808080;">JOIN</span> sys.trace_events TE <span style="color: #0000ff;">ON</span> T.EventClass <span style="color: #808080;">=</span><span style="color: #000000;"> TE.trace_event_id
  15. </span><span style="color: #0000ff;">WHERE</span> te.name <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Missing Column Statistics</span><span style="color: #ff0000;">‘</span>
  16. <span style="color: #808080;">OR</span> te.name <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Missing Join Predicate</span><span style="color: #ff0000;">‘</span>

 

Full text 事件
Full text事件类显示的主要事件包括：如果Full-Text终止，你可以在事件日志中找到具体的消息；FT Crawl Started子事件表明请求已经被进程获得。FT Crawl Stopped表明要么成功完成要么被错误停止。

 

Full-Text事件包括：

? FT Crawl Aborted

? FT Crawl Started

? FT Crawl Stopped

 

这个脚本返回全文事件：

  1. <span style="color: #0000ff;">SELECT</span> TE.name <span style="color: #0000ff;">AS</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">EventName</span><span style="color: #ff0000;">]</span><span style="color: #000000;"> ,
  2. </span><span style="color: #ff00ff;">DB_NAME</span>(t.DatabaseID) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> DatabaseName ,
  3. t.DatabaseID ,
  4. t.NTDomainName ,
  5. t.ApplicationName ,
  6. t.LoginName ,
  7. t.SPID ,
  8. t.StartTime ,
  9. t.IsSystem
  10. </span><span style="color: #0000ff;">FROM</span> sys.fn_trace_gettable(<span style="color: #ff00ff;">CONVERT</span>(<span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">150</span>), ( <span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">TOP</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">
  11. f.</span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">value</span><span style="color: #ff0000;">]</span>
  12. <span style="color: #0000ff;">FROM</span> sys.fn_trace_getinfo(<span style="color: #0000ff;">NULL</span><span style="color: #000000;">) f
  13. </span><span style="color: #0000ff;">WHERE</span> f.property <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;">
  14. )), </span><span style="color: #0000ff;">DEFAULT</span><span style="color: #000000;">) T
  15. </span><span style="color: #808080;">JOIN</span> sys.trace_events TE <span style="color: #0000ff;">ON</span> T.EventClass <span style="color: #808080;">=</span><span style="color: #000000;"> TE.trace_event_id
  16. </span><span style="color: #0000ff;">WHERE</span> te.name <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">FT:Crawl Started</span><span style="color: #ff0000;">‘</span>
  17. <span style="color: #808080;">OR</span> te.name <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">FT:Crawl Aborted</span><span style="color: #ff0000;">‘</span>
  18. <span style="color: #808080;">OR</span> te.name <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">FT:Crawl Stopped</span><span style="color: #ff0000;">‘</span>


注意，DatabaseName列是null，所以我们得用DB_NAME()函数获得数据库名。

 

Objects 事件
现在真正的探测工作开始：Objects改变。在子类中，有Altered、Created和Deleted对象。这里也包含了索引重建、统计信息更新，到数据库删除。

 

Objects事件包括：

? Object Altered

? Object Created

? Object Deleted

 

下面的脚本显示了数据库中最近的操作对象：

  1. <span style="color: #0000ff;">SELECT</span><span style="color: #000000;"> TE.name ,
  2. v.subclass_name ,
  3. </span><span style="color: #ff00ff;">DB_NAME</span>(t.DatabaseId) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> DBName ,
  4. T.NTDomainName ,
  5. t.NTUserName ,
  6. t.HostName ,
  7. t.ApplicationName ,
  8. t.LoginName ,
  9. t.Duration ,
  10. t.StartTime ,
  11. t.ObjectName ,
  12. </span><span style="color: #ff00ff;">CASE</span><span style="color: #000000;"> t.ObjectType
  13. </span><span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">8259</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Check Constraint</span><span style="color: #ff0000;">‘</span>
  14. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">8260</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Default (constraint or standalone)</span><span style="color: #ff0000;">‘</span>
  15. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">8262</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Foreign-key Constraint</span><span style="color: #ff0000;">‘</span>
  16. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">8272</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Stored Procedure</span><span style="color: #ff0000;">‘</span>
  17. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">8274</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Rule</span><span style="color: #ff0000;">‘</span>
  18. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">8275</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">System Table</span><span style="color: #ff0000;">‘</span>
  19. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">8276</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Trigger on Server</span><span style="color: #ff0000;">‘</span>
  20. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">8277</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">(User-defined) Table</span><span style="color: #ff0000;">‘</span>
  21. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">8278</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">View</span><span style="color: #ff0000;">‘</span>
  22. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">8280</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Extended Stored Procedure</span><span style="color: #ff0000;">‘</span>
  23. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">16724</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">CLR Trigger</span><span style="color: #ff0000;">‘</span>
  24. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">16964</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Database</span><span style="color: #ff0000;">‘</span>
  25. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">16975</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Object</span><span style="color: #ff0000;">‘</span>
  26. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">17222</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">FullText Catalog</span><span style="color: #ff0000;">‘</span>
  27. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">17232</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">CLR Stored Procedure</span><span style="color: #ff0000;">‘</span>
  28. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">17235</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Schema</span><span style="color: #ff0000;">‘</span>
  29. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">17475</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Credential</span><span style="color: #ff0000;">‘</span>
  30. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">17491</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">DDL Event</span><span style="color: #ff0000;">‘</span>
  31. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">17741</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Management Event</span><span style="color: #ff0000;">‘</span>
  32. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">17747</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Security Event</span><span style="color: #ff0000;">‘</span>
  33. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">17749</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">User Event</span><span style="color: #ff0000;">‘</span>
  34. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">17985</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">CLR Aggregate Function</span><span style="color: #ff0000;">‘</span>
  35. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">17993</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Inline Table-valued SQL Function</span><span style="color: #ff0000;">‘</span>
  36. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">18000</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Partition Function</span><span style="color: #ff0000;">‘</span>
  37. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">18002</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Replication Filter Procedure</span><span style="color: #ff0000;">‘</span>
  38. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">18004</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Table-valued SQL Function</span><span style="color: #ff0000;">‘</span>
  39. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">18259</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Server Role</span><span style="color: #ff0000;">‘</span>
  40. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">18263</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Microsoft Windows Group</span><span style="color: #ff0000;">‘</span>
  41. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">19265</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Asymmetric Key</span><span style="color: #ff0000;">‘</span>
  42. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">19277</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Master Key</span><span style="color: #ff0000;">‘</span>
  43. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">19280</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Primary Key</span><span style="color: #ff0000;">‘</span>
  44. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">19283</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">ObfusKey</span><span style="color: #ff0000;">‘</span>
  45. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">19521</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Asymmetric Key Login</span><span style="color: #ff0000;">‘</span>
  46. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">19523</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Certificate Login</span><span style="color: #ff0000;">‘</span>
  47. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">19538</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Role</span><span style="color: #ff0000;">‘</span>
  48. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">19539</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">SQL Login</span><span style="color: #ff0000;">‘</span>
  49. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">19543</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Windows Login</span><span style="color: #ff0000;">‘</span>
  50. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">20034</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Remote Service Binding</span><span style="color: #ff0000;">‘</span>
  51. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">20036</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Event Notification on Database</span><span style="color: #ff0000;">‘</span>
  52. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">20037</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Event Notification</span><span style="color: #ff0000;">‘</span>
  53. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">20038</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Scalar SQL Function</span><span style="color: #ff0000;">‘</span>
  54. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">20047</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Event Notification on Object</span><span style="color: #ff0000;">‘</span>
  55. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">20051</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Synonym</span><span style="color: #ff0000;">‘</span>
  56. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">20549</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">End Point</span><span style="color: #ff0000;">‘</span>
  57. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">20801</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Adhoc Queries which may be cached</span><span style="color: #ff0000;">‘</span>
  58. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">20816</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Prepared Queries which may be cached</span><span style="color: #ff0000;">‘</span>
  59. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">20819</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Service Broker Service Queue</span><span style="color: #ff0000;">‘</span>
  60. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">20821</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Unique Constraint</span><span style="color: #ff0000;">‘</span>
  61. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">21057</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Application Role</span><span style="color: #ff0000;">‘</span>
  62. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">21059</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Certificate</span><span style="color: #ff0000;">‘</span>
  63. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">21075</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Server</span><span style="color: #ff0000;">‘</span>
  64. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">21076</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Transact-SQL Trigger</span><span style="color: #ff0000;">‘</span>
  65. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">21313</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Assembly</span><span style="color: #ff0000;">‘</span>
  66. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">21318</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">CLR Scalar Function</span><span style="color: #ff0000;">‘</span>
  67. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">21321</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Inline scalar SQL Function</span><span style="color: #ff0000;">‘</span>
  68. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">21328</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Partition Scheme</span><span style="color: #ff0000;">‘</span>
  69. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">21333</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">User</span><span style="color: #ff0000;">‘</span>
  70. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">21571</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Service Broker Service Contract</span><span style="color: #ff0000;">‘</span>
  71. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">21572</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Trigger on Database</span><span style="color: #ff0000;">‘</span>
  72. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">21574</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">CLR Table-valued Function</span><span style="color: #ff0000;">‘</span>
  73. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">21577</span>
  74. <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Internal Table (For example, XML Node Table, Queue Table.)</span><span style="color: #ff0000;">‘</span>
  75. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">21581</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Service Broker Message Type</span><span style="color: #ff0000;">‘</span>
  76. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">21586</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Service Broker Route</span><span style="color: #ff0000;">‘</span>
  77. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">21587</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Statistics</span><span style="color: #ff0000;">‘</span>
  78. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">21825</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">User</span><span style="color: #ff0000;">‘</span>
  79. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">21827</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">User</span><span style="color: #ff0000;">‘</span>
  80. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">21831</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">User</span><span style="color: #ff0000;">‘</span>
  81. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">21843</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">User</span><span style="color: #ff0000;">‘</span>
  82. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">21847</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">User</span><span style="color: #ff0000;">‘</span>
  83. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">22099</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Service Broker Service</span><span style="color: #ff0000;">‘</span>
  84. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">22601</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Index</span><span style="color: #ff0000;">‘</span>
  85. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">22604</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Certificate Login</span><span style="color: #ff0000;">‘</span>
  86. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">22611</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">XMLSchema</span><span style="color: #ff0000;">‘</span>
  87. <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">22868</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Type</span><span style="color: #ff0000;">‘</span>
  88. <span style="color: #0000ff;">ELSE</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Hmmm???</span><span style="color: #ff0000;">‘</span>
  89. <span style="color: #0000ff;">END</span> <span style="color: #0000ff;">AS</span><span style="color: #000000;"> ObjectType
  90. </span><span style="color: #0000ff;">FROM</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">fn_trace_gettable</span><span style="color: #ff0000;">]</span>(<span style="color: #ff00ff;">CONVERT</span>(<span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">150</span>), ( <span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">TOP</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">
  91. value
  92. </span><span style="color: #0000ff;">FROM</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">fn_trace_getinfo</span><span style="color: #ff0000;">]</span>(<span style="color: #0000ff;">NULL</span><span style="color: #000000;">)
  93. </span><span style="color: #0000ff;">WHERE</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">property</span><span style="color: #ff0000;">]</span> <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;">
  94. )), </span><span style="color: #0000ff;">DEFAULT</span><span style="color: #000000;">) T
  95. </span><span style="color: #808080;">JOIN</span> sys.trace_events TE <span style="color: #0000ff;">ON</span> T.EventClass <span style="color: #808080;">=</span><span style="color: #000000;"> TE.trace_event_id
  96. </span><span style="color: #808080;">JOIN</span> sys.trace_subclass_values v <span style="color: #0000ff;">ON</span> v.trace_event_id <span style="color: #808080;">=</span><span style="color: #000000;"> TE.trace_event_id
  97. </span><span style="color: #808080;">AND</span> v.subclass_value <span style="color: #808080;">=</span><span style="color: #000000;"> t.EventSubClass
  98. </span><span style="color: #0000ff;">WHERE</span> TE.name <span style="color: #808080;">IN</span> ( <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Object:Created</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Object:Deleted</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Object:Altered</span><span style="color: #ff0000;">‘</span><span style="color: #000000;"> )
  99. </span><span style="color: #008080;">--</span><span style="color: #008080;"> filter statistics created by SQL server</span>
  100. <span style="color: #808080;">AND</span> t.ObjectType <span style="color: #808080;">NOT</span> <span style="color: #808080;">IN</span> ( <span style="color: #800000; font-weight: bold;">21587</span><span style="color: #000000;"> )
  101. </span><span style="color: #008080;">--</span><span style="color: #008080;"> filter tempdb objects</span>
  102. <span style="color: #808080;">AND</span> DatabaseID <span style="color: #808080;"><></span> <span style="color: #800000; font-weight: bold;">2</span>
  103. <span style="color: #008080;">--</span><span style="color: #008080;"> get only events in the past 24 hours</span>
  104. <span style="color: #808080;">AND</span> StartTime <span style="color: #808080;">></span> <span style="color: #ff00ff;">DATEADD</span>(HH, <span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">24</span>, <span style="color: #ff00ff;">GETDATE</span><span style="color: #000000;">())
  105. ORDERBY t.StartTime </span><span style="color: #0000ff;">DESC</span> ;

 

记住，SQL Server默认有5个跟踪文件，每个20MB，没有知道的方法修改。如果你的系统很繁忙，跟踪文件会快速循环（甚至几小时内），以至你无法捕获到改变。

 

Security Audit事件
另一部分默认跟踪是Security Audit。你可以下列事件列表可以看到，它是一个丰富的默认跟踪。通常，这些时间组告诉我们的是在我们系统中发生的重要安全事件。

 

Security Audit事件包括：

? Audit Add DB user event

? Audit Add login to server role event

? Audit Add Member to DB role event

? Audit Add Role event

? Audit Add login event

? Audit Backup/Restore event

? Audit Change Database owner

? Audit DBCC event

? Audit Database Scope GDR event (Grant, Deny, Revoke)

? Audit Login Change Property event

? Audit Login Failed

? Audit Login GDR event

? Audit Schema Object GDR event

? Audit Schema Object Take Ownership

? Audit Server Starts and Stops

 

让我们一步步操作：

? 创建一个SQL Server login

? 分配读权限到数据库的用户

 

通过运行下面的脚本，我们能跟踪用户在实例上创建的对象。

  1. <span style="color: #0000ff;">SELECT</span> TE.name <span style="color: #0000ff;">AS</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">EventName</span><span style="color: #ff0000;">]</span><span style="color: #000000;"> ,
  2. v.subclass_name ,
  3. T.DatabaseName ,
  4. t.DatabaseID ,
  5. t.NTDomainName ,
  6. t.ApplicationName ,
  7. t.LoginName ,
  8. t.SPID ,
  9. t.StartTime ,
  10. t.RoleName ,
  11. t.TargetUserName ,
  12. t.TargetLoginName ,
  13. t.SessionLoginName
  14. </span><span style="color: #0000ff;">FROM</span> sys.fn_trace_gettable(<span style="color: #ff00ff;">CONVERT</span>(<span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">150</span>), ( <span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">TOP</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">
  15. f.</span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">value</span><span style="color: #ff0000;">]</span>
  16. <span style="color: #0000ff;">FROM</span> sys.fn_trace_getinfo(<span style="color: #0000ff;">NULL</span><span style="color: #000000;">) f
  17. </span><span style="color: #0000ff;">WHERE</span> f.property <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;">
  18. )), </span><span style="color: #0000ff;">DEFAULT</span><span style="color: #000000;">) T
  19. </span><span style="color: #808080;">JOIN</span> sys.trace_events TE <span style="color: #0000ff;">ON</span> T.EventClass <span style="color: #808080;">=</span><span style="color: #000000;"> TE.trace_event_id
  20. </span><span style="color: #808080;">JOIN</span> sys.trace_subclass_values v <span style="color: #0000ff;">ON</span> v.trace_event_id <span style="color: #808080;">=</span><span style="color: #000000;"> TE.trace_event_id
  21. </span><span style="color: #808080;">AND</span> v.subclass_value <span style="color: #808080;">=</span><span style="color: #000000;"> t.EventSubClass
  22. </span><span style="color: #0000ff;">WHERE</span> te.name <span style="color: #808080;">IN</span> ( <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Audit Addlogin Event</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Audit Add DB User Event</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">,
  23. </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Audit Add Member to DB Role Event</span><span style="color: #ff0000;">‘</span><span style="color: #000000;"> )
  24. </span><span style="color: #808080;">AND</span> v.subclass_name <span style="color: #808080;">IN</span> ( <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">add</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Grant database access</span><span style="color: #ff0000;">‘</span> )

 

下面是我们创建登录，并给用户赋予读取权限后的结果：

 

可以看到，第一行显示在master数据库中登录的创建，以及创建者（SessionLoginName列）和创建用户（TargetLoginName列）。

下两行：创建数据库用户并授予它访问权限，并最后添加用户到DB角色。

记住，如果你添加用户到多个角色，且如果你赋予登录访问给多个数据库，然而你会看到对每一个事件会记录很多行在你的默认跟踪。

现在，让我们审核被删除的用户和登录，运行如下查询语句：

  1. <span style="color: #0000ff;">SELECT</span> TE.name <span style="color: #0000ff;">AS</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">EventName</span><span style="color: #ff0000;">]</span><span style="color: #000000;"> ,
  2. v.subclass_name ,
  3. T.DatabaseName ,
  4. t.DatabaseID ,
  5. t.NTDomainName ,
  6. t.ApplicationName ,
  7. t.LoginName ,
  8. t.SPID ,
  9. t.StartTime ,
  10. t.RoleName ,
  11. t.TargetUserName ,
  12. t.TargetLoginName ,
  13. t.SessionLoginName
  14. </span><span style="color: #0000ff;">FROM</span> sys.fn_trace_gettable(<span style="color: #ff00ff;">CONVERT</span>(<span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">150</span>), ( <span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">TOP</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">
  15. f.</span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">value</span><span style="color: #ff0000;">]</span>
  16. <span style="color: #0000ff;">FROM</span> sys.fn_trace_getinfo(<span style="color: #0000ff;">NULL</span><span style="color: #000000;">) f
  17. </span><span style="color: #0000ff;">WHERE</span> f.property <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;">
  18. )), </span><span style="color: #0000ff;">DEFAULT</span><span style="color: #000000;">) T
  19. </span><span style="color: #808080;">JOIN</span> sys.trace_events TE <span style="color: #0000ff;">ON</span> T.EventClass <span style="color: #808080;">=</span><span style="color: #000000;"> TE.trace_event_id
  20. </span><span style="color: #808080;">JOIN</span> sys.trace_subclass_values v <span style="color: #0000ff;">ON</span> v.trace_event_id <span style="color: #808080;">=</span><span style="color: #000000;"> TE.trace_event_id
  21. </span><span style="color: #808080;">AND</span> v.subclass_value <span style="color: #808080;">=</span><span style="color: #000000;"> t.EventSubClass
  22. </span><span style="color: #0000ff;">WHERE</span> te.name <span style="color: #808080;">IN</span> ( <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Audit Addlogin Event</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Audit Add DB User Event</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">,
  23. </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Audit Add Member to DB Role Event</span><span style="color: #ff0000;">‘</span><span style="color: #000000;"> )
  24. </span><span style="color: #808080;">AND</span> v.subclass_name <span style="color: #808080;">IN</span> ( <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Drop</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Revoke database access</span><span style="color: #ff0000;">‘</span> )


可以看到，对于创建和删除登录，事件名是相同的：Audit Addlogin Event；然而子类列值定义的不同：在创建登录子类是“Add”，而在删除子类是“Drop”。

事实上，如果我们删除之前创建的数据库用户和登录，这个查询会返回两行—对于每一个事件返回一行，包含被删除的用户和登录的名字，以及删除者的登录名。

 

下面的查询显示在默认跟踪文件中所有的失败的登录：

  1. <span style="color: #0000ff;">SELECT</span> TE.name <span style="color: #0000ff;">AS</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">EventName</span><span style="color: #ff0000;">]</span><span style="color: #000000;"> ,
  2. v.subclass_name ,
  3. T.DatabaseName ,
  4. t.DatabaseID ,
  5. t.NTDomainName ,
  6. t.ApplicationName ,
  7. t.LoginName ,
  8. t.SPID ,
  9. t.StartTime ,
  10. t.SessionLoginName
  11. </span><span style="color: #0000ff;">FROM</span> sys.fn_trace_gettable(<span style="color: #ff00ff;">CONVERT</span>(<span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">150</span>), ( <span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">TOP</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">
  12. f.</span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">value</span><span style="color: #ff0000;">]</span>
  13. <span style="color: #0000ff;">FROM</span> sys.fn_trace_getinfo(<span style="color: #0000ff;">NULL</span><span style="color: #000000;">) f
  14. </span><span style="color: #0000ff;">WHERE</span> f.property <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">2</span>                   )), <span style="color: #0000ff;">DEFAULT</span><span style="color: #000000;">) T
  15. </span><span style="color: #808080;">JOIN</span> sys.trace_events TE <span style="color: #0000ff;">ON</span> T.EventClass <span style="color: #808080;">=</span><span style="color: #000000;"> TE.trace_event_id
  16. </span><span style="color: #808080;">JOIN</span> sys.trace_subclass_values v <span style="color: #0000ff;">ON</span> v.trace_event_id <span style="color: #808080;">=</span><span style="color: #000000;"> TE.trace_event_id
  17. </span><span style="color: #808080;">AND</span> v.subclass_value <span style="color: #808080;">=</span><span style="color: #000000;"> t.EventSubClass
  18. </span><span style="color: #0000ff;">WHERE</span>   te.name <span style="color: #808080;">IN</span> ( <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Audit Login Failed</span><span style="color: #ff0000;">‘</span> )


有大量的事件在Security Audit类，我们这里重点关注“Audit Server Starts and Stops”。

 

下面的查询将会列出服务器启动事件：

  1. <span style="color: #0000ff;">SELECT</span> TE.name <span style="color: #0000ff;">AS</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">EventName</span><span style="color: #ff0000;">]</span><span style="color: #000000;"> ,
  2. v.subclass_name ,
  3. T.DatabaseName ,
  4. t.DatabaseID ,
  5. t.NTDomainName ,
  6. t.ApplicationName ,
  7. t.LoginName ,
  8. t.SPID ,
  9. t.StartTime ,
  10. t.SessionLoginName
  11. </span><span style="color: #0000ff;">FROM</span> sys.fn_trace_gettable(<span style="color: #ff00ff;">CONVERT</span>(<span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">150</span>), ( <span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">TOP</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">
  12. f.</span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">value</span><span style="color: #ff0000;">]</span>
  13. <span style="color: #0000ff;">FROM</span> sys.fn_trace_getinfo(<span style="color: #0000ff;">NULL</span><span style="color: #000000;">) f
  14. </span><span style="color: #0000ff;">WHERE</span> f.property <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;">
  15. )), </span><span style="color: #0000ff;">DEFAULT</span><span style="color: #000000;">) T
  16. </span><span style="color: #808080;">JOIN</span> sys.trace_events TE <span style="color: #0000ff;">ON</span> T.EventClass <span style="color: #808080;">=</span><span style="color: #000000;"> TE.trace_event_id
  17. </span><span style="color: #808080;">JOIN</span> sys.trace_subclass_values v <span style="color: #0000ff;">ON</span> v.trace_event_id <span style="color: #808080;">=</span><span style="color: #000000;"> TE.trace_event_id
  18. </span><span style="color: #808080;">AND</span> v.subclass_value <span style="color: #808080;">=</span><span style="color: #000000;"> t.EventSubClass
  19. </span><span style="color: #0000ff;">WHERE</span>   te.name <span style="color: #808080;">IN</span> ( <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Audit Server Starts and Stops</span><span style="color: #ff0000;">‘</span> )


请注意：上面的查询只返回Server Start事件，而不会返回Server Stop事件。解释是这样的：像之前所提到的，SQL Server的默认跟踪总共有5个跟踪文件组成，每个文件最大20MB。这五个文件在一些条件下会循环：当实例启动，或当文件大小超过20MB。现在，让我们想一下：我们所列出的查询返回的结果仅来自于当前的跟踪文件，最新的那一个。而因为默认跟踪文件在服务器实例启动时被循环利用，意思是包含Server Stop的事件将保留在前一个默认跟踪文件中。简而言之，在SQL服务重启后，我们当前的默认跟踪文件将有Server Start事件作为第一行。如果你真的希望知道你的SQL Server实例什么时候停止的，你需要至少包含上一个文件的内容，但事实上我们能包含其他4个默认跟踪文件的内容到我们的结果集。我们可以通过调用sys.fn_trace_gettable的方法，他能追加所有的默认跟踪文件。这个函数接受2个参数—文件的位置和名称、以及文件的数量；如果我们传入最旧的默认跟踪文件的路径和名称到第1个参数，而sys.fn_trace_gettable会追加最新的，一旦我们设置了适当的值给第2个参数（文件数量）。如果传入最新的文件给这个函数，那么旧的文件不会被追加。因为文件名包含了文件创建时候的索引，因此很容易计算最旧文件的名称。

 

找到默认跟踪文件的真实路径，你需要执行一下语句：

  1. <span style="color: #0000ff;">SELECT</span> <span style="color: #ff00ff;">REVERSE</span>(<span style="color: #ff00ff;">SUBSTRING</span>(<span style="color: #ff00ff;">REVERSE</span>(path), <span style="color: #ff00ff;">CHARINDEX</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">\</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff00ff;">REVERSE</span>(path)), <span style="color: #800000; font-weight: bold;">256</span>)) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> DefaultTraceLocation
  2. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> sys.traces
  3. </span><span style="color: #0000ff;">WHERE</span> is_default <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1</span>

 

Server事件
最后一个事件类：Server类。它只包含了一个事件—Server Memory Change。

 

下面的查询显示了什么时候内存使用改变：

  1. <span style="color: #0000ff;">SELECT</span> TE.name <span style="color: #0000ff;">AS</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">EventName</span><span style="color: #ff0000;">]</span><span style="color: #000000;"> ,
  2. v.subclass_name ,
  3. t.IsSystem
  4. </span><span style="color: #0000ff;">FROM</span> sys.fn_trace_gettable(<span style="color: #ff00ff;">CONVERT</span>(<span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">150</span>), ( <span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">TOP</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">
  5. f.</span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">value</span><span style="color: #ff0000;">]</span>
  6. <span style="color: #0000ff;">FROM</span> sys.fn_trace_getinfo(<span style="color: #0000ff;">NULL</span><span style="color: #000000;">) f
  7. </span><span style="color: #0000ff;">WHERE</span> f.property <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;">
  8. )), </span><span style="color: #0000ff;">DEFAULT</span><span style="color: #000000;">) T
  9. </span><span style="color: #808080;">JOIN</span> sys.trace_events TE <span style="color: #0000ff;">ON</span> T.EventClass <span style="color: #808080;">=</span><span style="color: #000000;"> TE.trace_event_id
  10. </span><span style="color: #808080;">JOIN</span> sys.trace_subclass_values v <span style="color: #0000ff;">ON</span> v.trace_event_id <span style="color: #808080;">=</span><span style="color: #000000;"> TE.trace_event_id
  11. </span><span style="color: #808080;">AND</span> v.subclass_value <span style="color: #808080;">=</span><span style="color: #000000;"> t.EventSubClass
  12. </span><span style="color: #0000ff;">WHERE</span> te.name <span style="color: #808080;">IN</span> ( <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Server Memory Change</span><span style="color: #ff0000;">‘</span> )


该事件子类表明了是否内存增加或减少。

 

总结
默认跟踪是一个检查SQL Server实例的健康和安全的有效方法。有些陷阱需要记住—主要是文件循环和大小限制，但是修改这一块不是不可能。重要的是，上面的查询语句是从当前最新的默认跟踪文件中获取的结果。依赖于SQL Server实例的繁忙程度，有可能文件循环太快，而DBA无法捕获所有重要的事件；因此，自动化是需要的。

原文链接

SQL Server 默认跟踪(Trace)捕获事件详解

标签：delete   replicat   errorlog   node   name   位置   内存   date   rsh   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！