如何使用SQL Server 跟踪数据库变化

发布网友发布时间：2022-04-26 10:53

共2个回答

懂视网时间：2022-05-02 14:08

sp_configure ‘show advanced options‘ , 1 ; go reconfigure; go exec sp_configure ‘default trace enabled‘ , 1 ; go reconfigure; go

如果默认追踪已经启用，可以重新配置禁用默认追踪：

exec sp_configure ‘default trace enabled‘ , 0 ;
go
reconfigure;
go

exec sp_configure ‘show advanced options‘ , 0 ;
go
reconfigure;
go

三，查看默认追踪的信息

默认追踪记录的数据存储在文件中，可以从系统视图 sys.traces查看文件的路径，文件的大小（Size）和文件的更新方式等信息，追踪文件默认的扩展名是 .trc。

select id
 ,iif(status=1,‘running‘,‘stopped‘) as status
 ,path
 ,max_size
 ,start_time
 ,stop_time
 ,event_count
 ,max_files
 ,is_rowset
 ,is_rollover
 ,is_shutdown
 ,is_default
 ,buffer_count
 ,buffer_size as each_buffer_size
from sys.traces
where id=1

默认追踪有5个跟踪文件，每一个文件的最大size默认是20MB，SQL Server负责维护这5个文件，当实例重启的时候或者到达文件Size最大值的时候，SQL Server创建新的文件，将最早的跟踪文件删除，依次滚动（Rollover）更新。

四，查看追踪文件的内容

函数sys.fn_trace_gettable，用于从追踪文件中读取数据，以关系表的格式显式：

sys.fn_trace_gettable ( ‘filename‘ , number_files )

参数filename：用于指定追踪文件的名称，其值可以从系统视图sys.traces 中的path获取；

参数number_files：如果number_files 被指定为default，函数读取所有的滚动文件。

函数返回的是关系表，有效字段是：追踪关联的事件绑定的字段，

select * 
from sys.fn_trace_gettable(N‘C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLLoglog_4.trc‘,default)

五，查看默认追踪记录的事件列表

函数fn_trace_geteventinfo(trace_id)返回追踪关联的事件列表，使用该函数可以查看默认追踪记录的事件和事件的特定字段：

select categ.name as category,
 te.trace_event_id as event_id, 
 te.name as event_name,
 tc.trace_column_id as event_column_id,
 tc.name as column_name,
 tc.type_name as column_type
from sys.fn_trace_geteventinfo(1) as gei
inner join sys.trace_columns tc 
 on gei.columnid=tc.trace_column_id
inner join sys.trace_events te 
 on gei.eventid=te.trace_event_id
inner join sys.trace_categories categ
 on te.category_id=categ.category_id
order by category,event_id,event_column_id

六，查看事件和Category

Category用于组织事件（Event），在SQL Server 2012中，共有21个Category，180个Event，每个Event属于唯一的一个Category。

select tc.name as category,
 te.trace_event_id as event_id,
 te.name as event_name
from sys.trace_categories tc 
inner join sys.trace_events te 
 on tc.category_id=te.category_id
order by category,event_id

七，查看事件绑定的字段

在SQL Server 2012中，事件共有66个字段，但不是每个Event都能绑定所有的66个字段，每个Event能够绑定的字段是固定的，系统预先设置，用户不能修改，视图 sys.trace_event_bindings 用于显示每个事件绑定的字段。

select te.trace_event_id as event_id, 
 te.name as event_name,
 tc.trace_column_id as column_id,
 tc.name as column_name,
 tc.type_name as column_type
from sys.trace_event_bindings teb 
inner join sys.trace_columns tc 
 on teb.trace_column_id=tc.trace_column_id
inner join sys.trace_events te 
 on teb.trace_event_id=te.trace_event_id
order by event_id,column_id

八，使用SQL Server Profiler创建SQL Trace

在创建SQL Trace时，可以使用系统提供的存储过程来实现，但是，创建的过程繁琐，不直观。大家知道，SQL Server Profiler是一个可视化用于查看数据库活动的工具，但是，它也是一个用于创建SQL Trace的工具。使用SQL Server Profiler创建SQL Trace的过程十分简单，选择相应的事件和事件的字段之后，导出SQL Trace 的定义即可。

创建SQL Trace之后，点击File->Export->Scipt Trace Definition，就能把SQL Server Profiler用于创建SQL Trace的脚本代码导出。

技术分享图片

导出的脚本如下，不能直接使用，必须修改一处代码：在创建Trace时，指定存储追踪数据的File 或 Table，仅此而已。

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

-- Client side File and Table cannot be scripted
exec @rc = sp_trace_create @TraceID output, 0, N‘InsertFileNameHere‘, @maxfilesize, NULL 
if (@rc != 0) goto error

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on

--delete many commands here ---

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 1, 0, 6, N‘%drop%‘

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go

View Code

注：SQL Trace是被扩展事件取代的功能，在后续的版本中将会被移除，建议在以后的开发中使用扩展事件。

参考文档：

SQL Trace

Server-wide Configuration Catalog Views (Transact-SQL)

System Trace Functions

SQL Server 默认跟踪（Default Trace）

SQL Server中关于跟踪（Trace)那点事

SQL追踪

标签：style view 事件 nsa ide export data exe tle

热心网友时间：2022-05-02 11:16

1、找到SQL Server Profiler并登录

位置：单击开始--程序--Microsoft SQL Server--性能工具--SQL Server Profiler

或者登录后在下图的位置寻找：

2、然后，进入下面的界面：

输入相关信息，单击连接进入下一个界面。

3、如下图所示界面：

4、关键点在“事件选择部分”，如下图：

小注：

要注意“列筛选器”的使用！

我们可以对统计的字段进行筛选，单击任意一个列标题可以查看列的说明如下图：

详解：

需要过滤具体的列名值，则选择对应的列，在右边树形框录入具体的值，并必须选择排除不包含值的行哈。

各个选项的具体含义，如下：

TextDate 依赖于跟踪中捕获的事件类的文本值；

ApplicationName 创建 SQL Server 连接的客户端应用程序的名称。此列由该应用程序传递的值填充，而不是由所显示的程序名填充的；

NTusername Windows 用户名。

LoginName 用户的登录名(SQL Server 安全登录或 Windows 登录凭据，格式为“域\用户名”)

CPU 事件使用的 CPU 时间(毫秒)。

Reads 由服务器代表事件读取逻辑磁盘的次数。

Writes 由服务器代表事件写入物理磁盘的次数。

Duration 事件占用的时间。尽管服务器以微秒计算持续时间，SQL Server Profiler 却能够以毫秒为单位显示该值，具体情况取决于“工具”>“选项”对话框中的设置

ClientProcessID 调用 SQL Server 的应用程序的进程 ID。

SPID SQL Server 为客户端的相关进程分配的服务器进程 ID。

StratTime 事件(如果可用)的启动时间。

EndTime 事件结束的时间。对指示事件开始的事件类(例如 SQL:BatchStarting 或 SP:Starting)将不填充此列。

BinaryData 依赖于跟踪中捕获的事件类的二进制值。

然后，单击运行即可！