监控事件系列——SQL Trace（默认跟踪与自定义跟踪）
目录：
帮助与使用
dbcc help('?') --查看dbcc 所有命令，常规下只有32个常用的
dbcc TRACEON(2588) --指定了2588标记的话，你就可以看到未公开的dbcc命令了，同时你也可以看到各种命令参数的提示了。很多dbcc命令需要开启它才能使用，比如ind，page等
dbcc help('checkdb') --帮助命令，使用其来查看命令用法，开启了上面的2588就可以用ind（查看堆表/索引组织表的数据也信息）了
dbcc TRACEON(3604) -- 指定标记，使得dbcc page的结果可以在客户端上显示出来
【1】服务器端跟踪（Server Side Trace）
【1.1】概念与使用
（可参考：http://www.mssqlmct.cn/dba/?post=311）
SQL跟踪任务：https://docs.microsoft.com/en-us/sql/relational-databases/sql-trace/sql-trace?view=sql-server-2017
-
使用sp_trace_create创建跟踪。
-
使用sp_trace_setevent添加事件。
-
（可选）使用sp_trace_setfilter设置过滤器。
-
使用sp_trace_setstatus启动跟踪。
-
使用sp_trace_setstatus停止跟踪。
-
使用sp_trace_setstatus关闭跟踪。
sp_trace_create（Transact-SQL）
sp_trace_setevent（Transact-SQL）
sp_trace_setfilter（Transact-SQL）
sp_trace_setstatus（Transact-SQL）
【1.2】跟踪的基本操作
--【1.2.1】查看默认跟踪是否在运行
select * from sys.configurations where configuration_id = 1568
--【1.2.2】开启默认跟踪
sp_configure 'show advanced options',1 go reconfigure; go sp_configure 'default trace enabled',1 go reconfigure; go
--【1.2.3】关闭默认跟踪
--default trace enabled 默认为1，为启动默认跟踪。0时关闭耿总
exec sp_configure 'default trace enabled',0;
reconfigure with override;
go
--关闭Default Trace sp_configure 'default trace enabled' , 0 ; GO RECONFIGURE; GO sp_configure 'show advanced options' , 0 ; GO RECONFIGURE; GO
--【1.2.4】查看跟踪文件/查看跟踪信息
select * from fn_trace_getinfo(default); --查看所有跟踪信息,会列出所有保存到文件的跟踪，property,1=Trace option. 2=File name. 3=Max size. 4=Stop time. 5=Current trace status(1 is enable)
1：trace options，有2（滚动文件）、4、8（黑盒）三个值，请参考sp_trace_create；
2：file name，更准确来说是trace文件的路径；
3：max file size，设置最大滚动文件大小，当达到这个值就会创建新的滚动文件；
4：stop time，设置trace停止的时间；
5：当前状态(0=stopped, 1=running) ；
select * from fn_trace_gettable('e:/DbLog/deadlockdetect.trc',1) --查看跟踪文件： T-SQL方式
select * from sys.traces --查看所有跟踪信息（推荐使用这个查看）
--【1.2.5】捕获事件详解
select * from sys.trace_events --查看跟踪事件
select * from sys.trace_categories --查看跟踪类别
select * from sys.trace_subclass_values --查看跟踪子类
【1.3】七大事件监控
（以下演示基于系统数据库，有需要可以自行修改调整）
Database、Errors and Warnings、Full text、Objects、Performance、Security Audit 和 Server
--【1.3.1】Database (1)Date file auto grow/shrink
(2)Log file auto grow/shrink
(3)Database mirroring status change 查看数据文件和日志的自动增长和收缩,数据库镜像的改变
--下面是从默认约束中，查看数据文件的增长和收缩
select t2.name as eventname,t1.databasename,t1.databaseId,t1.NTDoMainName,t1.applicationName,t1.loginName,t1.spid,t1.Duration as 'Duration(us)',t1.startTime,t1.endtime
from sys.fn_trace_gettable((select path from sys.traces where id=1 ),default) t1
join sys.trace_events t2 on t1.eventClass=t2.trace_event_id
where t2.name in ('Data File Auto Grow','Data File Auto Shrink') -- 要查看日志，把下面的name in 改成 log File Auto Grow 等即可
--【1.3.2】Errors and Warnings
(1)Errorlog (2)Hash warning (3)Missing Column Statistics
(4)Missing join Predicate (5)Sort Waring
--查看语句，如果要修改，直接把t2.name= 上面的(1)-(5)即可
select t2.name,t1.databasename,t1.databaseId,t1.applicationName,t1.NTDomainName,t1.loginName,t1.spid,t1.StartTime,t1.textData,t1.Severity,t1.error from sys.fn_trace_gettable((select path from sys.traces where id =1),default) t1 join sys.trace_events t2 on t1.eventClass=t2.trace_event_id where t2.name='errorlog'
--【1.3.3】Full Text
(1)FT Crawl Aborted (2)FT Crawl Started (3)FT Crawl Stopped
--查看全文索引
select t2.name,t1.databasename,t1.databaseId,t1.applicationName,t1.NTDomainName, t1.loginName,t1.spid,t1.StartTime,t1.textData,t1.Severity,t1.error,t1.isSystem from sys.fn_trace_gettable((select path from sys.traces where id =1),default) t1 join sys.trace_events t2 on t1.eventClass=t2.trace_event_id where t2.name='FT:Crawl started' or t2.name = 'FT:Crawl Aborted' or t2.name='FT:Crawl Stopped'
--【1.3.4】objects
(1)Object Altered (2)Object Created (3)Object Deleted
--查看对象操作
select t2.name,t1.databasename,t1.databaseId,t1.applicationName,t1.NTDomainName,
t1.loginName,t1.spid,t1.StartTime,t1.duration,t1.textData,t1.Severity,t1.error,t1.isSystem,t1.objectType
from sys.fn_trace_gettable((select path from sys.traces where id =1),default) t1
join sys.trace_events t2 on t1.eventClass=t2.trace_event_id
join sys.trace_subclass_values t3 on t3.trace_event_id = t2.trace_event_id and t3.SubClass_value=t1.eventSubClass
where t2.name in ('Object:Created','Object:Deleted','Object:Altered')
and t1.objectType not in (21587) --filter statistics created by Sql server
and databaseId <> 2 --filter tempdb objects
and starttime>dateadd(hh,-24,getdate()) --get only events in the past 24hours
--【1.3.5】Security Audit
Audit add DB user event.
Audit add Login to server role event.
Audit add Member to DB role eventt.
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.
--查看创建login，创建user
select t2.name as eventname,t3.subclass_name,t1.databasename,t1.databaseid,t1.NTDomainName,t1.ApplicationName,
t1.LoginName,t1.SPid,t1.starttime,t1.roleName,t1.targetUserName,t1.targetLoginName,t1.sessionLoginname,t1.textdata
from sys.fn_trace_gettable((select path from sys.traces where id =1),default) t1
join sys.trace_events t2 on t1.eventClass=t2.trace_event_id
join sys.trace_subclass_values t3 on t3.trace_event_id = t2.trace_event_id and t3.SubClass_value=t1.eventSubClass
where t2.name in ('Audit AddLogin Event','Audit Add DB User Event','Audit Add Member to DB Role Event')
AND t3.subclass_name in ('add','Grant database access');
--AND t3.subclass_name in ('Drop','Remove database access') 删除登录
--去掉 t3.subclass_name，t2.name in ('Audit Login Failed') 审计登录失败
--去掉 t3.subclass_name，t2.name in ('Audit Server Starts and Stops') 审计服务器启动事件，只会显示启动（因为在重启服务时，会自动轮询默认跟踪文件，若想看之前的信息，可以查看其它的跟踪文件）
--【1.3.6】Server事件
它只包含了一个时间——Server Memory Change.
--内存使用改变，表明内存是否增加或者减少
select t2.name as [eventName],t3.subclass_name,t1.IsSystem
from sys.fn_trace_gettable((select path from sys.traces where id =1),default) t1
join sys.trace_events t2 on t1.eventClass=t2.trace_event_id
join sys.trace_subclass_values t3 on t3.trace_event_id = t2.trace_event_id and t3.SubClass_value=t1.eventSubClass
where t2.name in ('Server Memory Change')
--【1.3.7】查看跟踪标识对应事件
--获取某个Trace跟踪了哪些Event和column
DECLARE @traceid INT
SET @traceid = 1
SELECT TCA.category_id,TCA.name AS category_name
,TE.trace_event_id,TE.name AS trace_event_name
,TCO.trace_column_id,TCO.name AS trace_column_name
FROM fn_trace_geteventinfo(@traceid) AS EI
LEFT JOIN sys.trace_events AS TE
ON EI.eventid = TE.trace_event_id
LEFT JOIN sys.trace_categories AS TCA
ON TE.category_id = TCA.category_id
LEFT JOIN sys.trace_columns AS TCO
ON EI.columnid = TCO.trace_column_id
GO
总结：默认跟踪是一个检查SQL Server实例是否健康和安全的有效办法。有些点需要记住：
（1）文件循环和大小限制：5个文件，每个20M，循环生成覆盖
（2）上面的查询语句只是从当前左心的默认跟踪文件获取的结果。依赖于MSSQL实例的繁忙程度。
可能会出现文件循环太快，导致DBA无法捕获所有重要的时间，因此自动化是很有必要的。
【2】默认跟踪的应用
【2.1】常用事件跟踪（删除、审核登录、DDL）
【2.1.1】删除对象事件
--如何查找从特定数据库在24H内被删除的对象？
--如何知道谁删除了数据库？
select t2.name,t1.databasename,t1.databaseId,t1.objectName,t1.applicationName,t1.NTDomainName,
t1.loginName,t1.spid,t1.StartTime,t1.duration,t1.textData,t1.Severity,t1.error,t1.isSystem,t1.objectType
from sys.fn_trace_gettable((select path from sys.traces where id =1),default) t1
join sys.trace_events t2 on t1.eventClass=t2.trace_event_id
join sys.trace_subclass_values t3 on t3.trace_event_id = t2.trace_event_id and t3.SubClass_value=t1.eventSubClass
where t2.name in ('Object:Created','Object:Deleted','Object:Altered')
and t1.objectType not in (21587) --filter statistics created by Sql server
and databaseId <> 2 --filter tempdb objects
and starttime>dateadd(hh,-24,getdate()) --get only events in the past 24hours
【2.1.2】审核备份和恢复事件
--默认跟踪 Audit Backup/Restore event,但执行备份和恢复操作将会触发该事件
--定位备份恢复问题
(1)用于备份和恢复的登录名是什么？
(2)操作是什么时候启动的？
(3)命令内容是什么？
(4)哪个应用程序发出的请求？
select t1.hostname,t2.name,t1.databasename,t1.databaseId,t1.applicationName,t1.NTDomainName, t1.loginName,t1.ntusername,t1.spid,t1.StartTime,t1.clientProcessid,t1.error,t1.textData,t1.Severity,t1.error,t1.isSystem from sys.fn_trace_gettable((select path from sys.traces where id =1),default) t1 join sys.trace_events t2 on t1.eventClass=t2.trace_event_id where t1.eventclass=115
【2.1.3】检测对表的ddl操作
select t1.hostname,t2.name,t1.databasename,t1.databaseId,t1.objectid,t1.objectName,t1.applicationName,t1.NTDomainName, t1.loginName,t1.ntusername,t1.spid,t1.StartTime,t1.clientProcessid,t1.error,t1.textData,t1.Severity,t1.error,t1.isSystem from sys.fn_trace_gettable((select path from sys.traces where id =1),default) t1 join sys.trace_events t2 on t1.eventClass=t2.trace_event_id where t1.eventclass in (46,47,164) -- select * from sys.trace_events where trace_event_id in (46,47,164)
AND databaseid !=2 --排除tempDB
【2.1.4】检测文件自动增长
select t2.name as [trace_events_name], t1.databaseName, t1.NTDomainName, t1.ApplicationName, t1.LoginName, t1.Duration, t1.StartTime, t1.EndTime from sys.fn_trace_gettable((select path from sys.traces where id = 1),default) t1 join sys.trace_events t2 on t1.eventClass=t2.trace_event_id where t2.trace_event_id = 93
数据库运行慢，是否因为文件增长耗时太长呢？有些因素会导致Log File Auto Grow性能较差：
（1）大事务导致事务增长，事务等待日志增长完成
（2）自动增长与收缩可能导致冲突
（3）衡量在日志文件上的物理碎片
（4）文件立刻初始化，填0初始化。
（5）最小化事务的大小。
（6）跟进存储性能，收集perfMon性能计数器。例如：Logical Write Bytes/sec和 Logical Read Bytes/sec
【2.1.5】SQL Server 默认跟踪报表
项目地址：http://sqlconcept.com/tools/default-trace-audit-documentation-and-faq/
安装完之后。右击ssms实例-》报表-》自定义报表-》然后选择安装目录下的DefaultTraceAudit_Main.rdl文件
默认跟踪表将在SSMS中打开，单机相应链接即可。
【2.2】服务端跟踪死锁案例
--【2.2.1】开启设置跟踪
use db_tank
go
--创建跟踪文件返回值
declare @rc int
--创建一个跟踪句柄
declare @TraceID int
--创建跟踪文件路径
declare @TraceFilePath nvarchar(500)
set @TraceFilePath=N'D:\DBA_TOOLS\db_deadLock_log'
--跟踪文件的大小
declare @maxfilesize bigint
set @maxfilesize=200
--设置停止的时间
declare @EndTime datetime
set @EndTime=null
--设置系统默认的操作
declare @options int
set @options=2
--设置默认滚动文件的数目
declare @filecount int
set @filecount=5
exec @rc=sp_trace_Create
@TraceID output,
@options,
@TraceFilePath,
@maxfilesize,
@EndTime,
@filecount
if(@rc=0)
declare @on bit
set @on = 1
--下述语句中的148指的是locks:deadlock graph事件(参见sys.trace_events),12指的是spid列(参见sys.trace_columns)
exec sp_trace_setevent @TraceID, 148, 12, @on
exec sp_trace_setevent @TraceID, 148, 11, @on
exec sp_trace_setevent @TraceID, 148, 4, @on
exec sp_trace_setevent @TraceID, 148, 14, @on
exec sp_trace_setevent @TraceID, 148, 26, @on
exec sp_trace_setevent @TraceID, 148, 64, @on
exec sp_trace_setevent @TraceID, 148, 1, @on
-- 启动跟踪
exec sp_trace_setstatus @TraceID, 1
-- 记录下跟踪ID，以备后面使用
select TraceID = @TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
--【2.2.2】启停与重启跟踪
-----默认重启实例后，跟踪会取消，所以写一个SP做实例启动执行
use master
go
create proc StartBlackBoxTrace
as
begin
【2.2.1】中代码
END
GO
--将该存储过程设置为SQL Server服务启动时自动启动
EXEC sp_procoption
'StartBlackBoxTrace','STARTUP','ON'
print 'ok'
GO
--查看
select * from sys.traces
--删除，exec sp_trace_setstatus @TraceID, 1 --0为停止跟踪，1为启用跟踪，2为删除跟踪，
--下面的2，为sys.traces中查出来的TraceId
exec sp_trace_setstatus 2, 0
exec sp_trace_setstatus 2, 2
【3】T-SQL使用跟踪
【3.1】跟踪的系统表
select * from sys.trace_categories select * from sys.trace_columns select * from sys.trace_event_bindings select * from sys.trace_events select * from sys.trace_subclass_values select * from sys.traces
【3.2】操作的SP
（可参考：http://www.mssqlmct.cn/dba/?post=311）
SQL跟踪任务：https://docs.microsoft.com/en-us/sql/relational-databases/sql-trace/sql-trace?view=sql-server-2017
-
使用sp_trace_create创建跟踪。
-
使用sp_trace_setevent添加事件。
-
（可选）使用sp_trace_setfilter设置过滤器。
-
使用sp_trace_setstatus启动跟踪。
-
使用sp_trace_setstatus停止跟踪。
-
使用sp_trace_setstatus关闭跟踪。
创建跟踪 (Transact-SQL)
sp_trace_create (Transact-SQL)
sp_trace_generateevent (Transact-sql)
sp_trace_setevent (Transact-SQL)
sp_trace_setfilter (Transact-SQL)
sp_trace_setstatus (Transact-SQL)
sys.fn_trace_getfilterinfo (Transact-SQL)
sys.fn_trace_geteventinfo (Transact-sql)
sys.fn_trace_gettable (Transact-sql)
【3.3】跟踪分析
以【2.2】案例分析；
（1）创建一个跟踪
exec @rc=sp_trace_Create @TraceID output, @options, @TraceFilePath, @maxfilesize, @EndTime, @filecount
（2）添加跟踪事件与对应显示的列字段
exec sp_trace_setevent @TraceID, 148, 12, @on
它的一般形式是
sp_trace_setevent [ @traceid = ] trace_id
, [ @eventid = ] event_id
, [ @columnid = ] column_id
, [ @on = ] on
我们可以发现，这个 @eventid，也就是上面的 148
指的是这个系统视图中的事件：
select * from sys.trace_events where trace_event_id=148
后面的列名，很明显就是 sys.trace_columns 里面的了，比如我们这里的12，就是SPID
最后的 @on，就是启用，因为前面定义了，所以直接写1也行
declare @on bit set @on = 1
【3.4】过滤 sp_trace_setfilter
sp_trace_setfilter [ @traceid = ] trace_id
, [ @columnid = ] column_id
, [ @logical_operator = ] logical_operator
, [ @comparison_operator = ] comparison_operator
, [ @value = ] value
[ @traceid = ] trace_id 要设置筛选器的跟踪的 ID。 trace_id 为 int，没有默认值。 用户使用此 trace_id 值标识、修改和控制跟踪。
[ @columnid = ] column_id 应用筛选器的列的 ID。 column_id 为 int，没有默认值。 如果 column_id 为 NULL，则 SQL Server 将清除指定跟踪的所有筛选器。
[ @logical_operator = ] logical_operator 指定是否应用 and (0) or (1) 运算符。 logical_operator 为 int，没有默认值。
[ @comparison_operator = ] comparison_operator 指定要进行的比较的类型。 comparison_operator 为 int，没有默认值。 下表包含比较运算符及其代表的值。
举例：
以下示例对 Trace 1 设置三个筛选器。 筛选器 N'SQLT%' 和 N'MS%' 使用“AppName”比较运算符对一列（10，其值为 LIKE）进行操作。
筛选器 N'joe' 使用“UserName”比较运算符对另一列（11，其值为 EQUAL）进行操作。
sp_trace_setfilter 1, 10, 0, 6, N'SQLT%'; sp_trace_setfilter 1, 10, 0, 6, N'MS%'; sp_trace_setfilter 1, 11, 0, 0, N'joe';
【3.5】启动、暂停、关闭跟踪 sp_trace_setstatus
sp_trace_setstatus [ @traceid = ] trace_id , [ @status = ] status
【参考文档】
同类文章：https://www.cnblogs.com/gaizai/p/3358998.html
SQL Server 2005 - Default Trace (默认跟踪)
default trace enabled (Option)
fn_trace_gettable (Transact-SQL)
