SQL Server基础之默认跟踪

用过sql server profiler的都知道，它可以跟踪sql server，并将sql server执行过的sql显示在界面。默认跟踪（default trace）便是集成在sql server中，和sql server profiler功能类似的一项服务，它的结果记录在log.trc文件中。顾名思义，默认跟踪（default trace）默认开启，若不想使用需要手动关闭。

--查看默认跟踪是否开启，value=1表示开启。
select value from sys.configurations where configuration_id = 1568

--开启默认跟踪
sp_configure 'show advanced options' , 1 ;
go
reconfigure;
go
sp_configure 'default trace enabled' , 1 ;
go
reconfigure;
go

--关闭默认跟踪
sp_configure 'default trace enabled' , 0 ;
go
reconfigure;
go
sp_configure 'show advanced options' , 0 ;
go
reconfigure;
go

sql server只维护5个log.trc文件，每个最大20m，实行滚动更新机制，即在5个文件都达到20m或者sql server实例重启时，删除最早建立的，重新生成一个新的log.trc文件。因为这个规则，导致只能查询到最近一段时间的跟踪记录，并且sql server越忙碌，可查询时间越短。这当然成为默认跟踪的一个缺点。不过，可以通过使用代理定时读取log.trc文件或者在服务器上使用小软件定时将log.trc文件复制到其他目录的方式进行弥补。

--获取正在使用的log.trc文件路径
--:: 是必须的。
select value from ::fn_trace_getinfo(0) where property=2

--读取正在被sql server使用的log.trc跟踪文件
--此段sql将返回一个行集
declare @logtrcwenjianlujing nvarchar(4000)
select @logtrcwenjianlujing=convert(nvarchar(4000),value) from ::fn_trace_getinfo(0) where property=2
select * from ::fn_trace_gettable(@logtrcwenjianlujing,default)

--此段sql读取最早一个trc文件

--获取正在使用的trc文件路径
declare @zhengzaishiyongdetrcwenjianlujing nvarchar(4000)
select @zhengzaishiyongdetrcwenjianlujing=convert(nvarchar(4000),value) from ::fn_trace_getinfo(0) where property=2

/*默认跟踪结果文件都是类似"log_87.trc"这样的文件名,log_+数字，其后数字滚动加一，即创建新文件时，它的命名是"log_88.trc"
根据这个特性，可以逆推取得最早一个trc文件*/
declare @weizhi int
declare @bianliangchangdu int
set @bianliangchangdu=len(@zhengzaishiyongdetrcwenjianlujing)
set @weizhi= @bianliangchangdu-charindex('\',reverse(@zhengzaishiyongdetrcwenjianlujing))+1

--获取的除去.trc后缀的文件名
declare @zhengzaishiyongdetrcwenjianming nvarchar(250)
select @zhengzaishiyongdetrcwenjianming=replace(lower(right(@zhengzaishiyongdetrcwenjianlujing,@bianliangchangdu-@weizhi)),'.trc','')

--获取正在使用的文件名中的数字并减去4组合log_获得最早一个trc文件名
declare @zuizaotrcwenjianming nvarchar(250)
set @zuizaotrcwenjianming='log_'+convert(nvarchar(250), convert(int,right(@zhengzaishiyongdetrcwenjianming,len(@zhengzaishiyongdetrcwenjianming)-charindex('_',@zhengzaishiyongdetrcwenjianming))-4))

--获取最早文件路径
declare @zuizaotrcwenjianlujing nvarchar(4000)
select @zuizaotrcwenjianlujing=replace (@zhengzaishiyongdetrcwenjianlujing,@zhengzaishiyongdetrcwenjianming,@zuizaotrcwenjianming)

--读取最早一个trc文件
select * from ::fn_trace_gettable(@zuizaotrcwenjianlujing,default)

默认跟踪较sql server profile轻量许多，自然也没有sql server profile功能强大。它不会跟踪所有事件，但比较关键的信息，如：auditing events、database event、serror events、full text events、object creation、object deletion、object alteration，都有跟踪,足以满足基本需求。

参考文献：

https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms173875%28v%3dsql.105%29

https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms188425(v%3dsql.105)

=====================================================================================
本文只代表本人的见解，可能存在错误，仅用于技术交流。如果你喜欢该文，可以扫下面的二维码打赏我（打赏敬请备注“博客园打赏”五字）。

SQL Server基础之默认跟踪

相关推荐