关于SQL Server默认跟踪和扩展事件 [英] About SQL Server Default trace and extended events

问题描述

我是SQL服务器的新手，我想问一下你对SQL Server默认跟踪和扩展事件的建议。以下是我的问题，谢谢大家！



1.关于默认跟踪 

 通常启用SQL Server的默认跟踪，我知道有五个文件来保存默认跟踪的记录，当文件达到数据大小限制时，它会将跟踪记录保存在下一个文件中，当第五个文件时数据大小达到
的限制，它会覆盖第一个文件。这是我的默认跟踪。

 一个。默认跟踪中将记录哪个事件或操作？如创建SQL服务器登录用户，创建某个数据库访问用户，插入语句，删除语句，更新语句，删除对象，更改表，更改视图，更改过程等，可以将
记录在默认跟踪中吗？

 湾如果某些上述操作无法记录在默认跟踪中，我们是否可以更改设置或脚本以记录我们要记录的操作？

  C。默认跟踪记录选择语句或执行存储过程的语句  ？例如，如果我想监视运行某些SQL语句或存储过程的持续时间。

  d。当我们使用SQL分析器跟踪操作时，有一个复选框让我们选择是否显示所有列，现在我想知道我们是否可以让默认跟踪记录我们需要的列？

  ;即我知道有5个文件要记录到跟踪日志，如果最后一个文件达到数据大小限制，那么覆盖第一个文件，由于需要进行性能监控，我想要的文件不能被覆盖，如果是第五个文件已满，则日志
将记录在第六个文件中，当第六个文件已满时记录在第七个文件中...，如何进行设置并让跟踪日志无法覆盖？   

  F。完成监控后，我想更改回跟踪日志文件设置，让跟踪日志文件在达到数据大小限制时可以被覆盖，如何更改回来？ 

 

2.延期活动

 一个。自2012年以来，在Extended events / Sessions / system_health下有Package0.event_file和Package0.ring_buffer来记录系统默认的扩展事件记录，我想知道

     记录可以保存（保存）多长时间，或者可以随时保存（保存）作为普通表的记录？

 湾哪个表用于保存扩展事件的记录？

  C。我知道用户可以创建自己的事件，在用户创建自己的事件后，扩展事件的记录也会记录在同一个表或相同的文件中，包含Package0.event_file和Package0.ring_buffer？

   

I'm a newibe of SQL server, I'd like to ask your advice on SQL Server default Trace and Extended Events. and below are my questions, thanks everyone !

1. About default trace 
  Normally the default trace of SQL Server is enabled, and I know there are five files to save the records of default trace, when the file reaches the data size limit, it will save the trace record in the next file, and when the fifth file data size reaches the limit, it wil overwrite the first file. here are my conerns of default trace.
  a. Which event or operation will be recorded in default trace? such as create SQL server login user、Create certain database access user、insert statement、delete statement、update statement、drop object、alter table、alter view、alter procedure and so on, can they be recorded in default trace?
  b. if some of the said operation can't be recorded in default trace, could we change the setting or script to record the operation we want to record ?
  c. Does default trace record select statement or the statement to execute a stored procedure  ? for example, If I want to monitor the duration time of runing some SQL statement or stored procedure.
  d. when we use SQL profiler to trace the operation,there is a checkbox to let us choose if to show all columns, now I want to know if we can let default trace record the column we need?
  e. I know there are 5 files to record to trace log, if the last file reaches the data size limit, then overwrite the first file, due to the need of performance monitoring, I want to the file can't be overwritten, if the fifth file is full, then the log will be recorded in the sixth file, when the sixth file is full then recorded in the seventh file..., how to do the setting and let trace log can't be overwritten ?  
  f. after finish the monitoring, I want to change back the trace log file setting to let the trace log file can be overwritten when it reach the data size limit, how to change it back? 
 
2. Extended Events
  a. there is Package0.event_file and Package0.ring_buffer under Extended events/Sessions/system_health to record system default extended event record since 2012, I want to know
      how long the records can be kept(saved) or it can be always kept(saved) as the record of common table?
  b. which table is used for saving the records of extended events?
  c. I know user can create own events, after user creates own events, the record of extended event is also recorded in the same table or same file with Package0.event_file and Package0.ring_buffer ?
   

推荐答案

由于我不记得确切的跟踪内容，我去了
http://www.google.com 并在搜索框中输入：

    sql server中的默认跟踪

    default trace in sql server

有几个匹配看起来很有趣。

There are several hits that look interesting.

如果你想确切地看到默认跟踪捕获的内容，你可以运行

If you want to see exactly what the default trace is capturing, you can run

SELECT * FROM sys.fn_trace_geteventinfo（1）

SELECT * FROM sys.fn_trace_geteventinfo(1)

然后将其连接到sys.trace_events和sys.trace_columns以获取明文名称。

And then join that to sys.trace_events and sys.trace_columns to get names in clear text.

我将有关扩展事件的问题留给其他人。

I leave the questions about extended events to someone else.

这篇关于关于SQL Server默认跟踪和扩展事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！