SQL Server中关于跟踪(Trace)那点事
前言
一提到跟踪俩字,很多人想到警匪片中的场景,同样在我们的SQL Server数据库中“跟踪”也是无处不在的,如果我们利用好了跟踪技巧,就可以针对某些特定的场景做定向分析,找出充足的证据来破案。
简单的举几个应用场景:
在线生产库为何突然宕机?数百张数据表为何不翼而飞?刚打好补丁的系统为何屡遭黑手?新添加的信息表为何频频丢失?某张表字段的突然更改,究竟为何人所为?这些个匿名的访问背后,究竟是人是鬼?突然增加的增量数据,究竟是对是错?数百兆的日志爆炸式的增长背后又隐藏着什么?这一且的背后,是应用程序的BUG还是用户品质的缺失?
请关注本篇文章,让我们一起利用数据库的“跟踪”(Trace)走进数据库背后,查看其内部原理。
我相信如用过SQL Server数据库的人,都会或多或少的利用过SQL Profiler工具。这个玩意就是利用SQL Trace形成的一个图形化操作工具,我们直接进入本篇的正题。
一.查看系统默认跟踪信息(Default Trace)
Trace作为一个很好的数据库追踪工具,在SQL Server 2005中便集成到系统功能中去,并且默认是开启的,当然我们也可以手动的关掉它,它位于sp_config配置参数中,我们可以通过以下语句查看:
select * from sys.configurations where configuration_id = 1568
我们也可以通过下面的语句找到这个跟踪的记录
select * from sys.traces
如果没有开启,我们也可以利用如下语句进行开启,或者关闭等操作
--开启Default Trace sp_configure 'show advanced options' , 1 ; GO RECONFIGURE; GO sp_configure 'default trace enabled' , 1 ; GO RECONFIGURE; GO --测试是否开启 EXEC sp_configure 'default trace enabled'; GO --关闭Default Trace sp_configure 'default trace enabled' , 0 ; GO RECONFIGURE; GO sp_configure 'show advanced options' , 0 ; GO RECONFIGURE; GO
通过以下命令找到默认跟踪的文件路径
select * from ::fn_trace_getinfo(0)
以上命令返回的结果值,各个值(property)代表的含义如下:
第一个:2表示滚动文件;
第二个:表示当前使用的trace文件路径,根据它我们可以找到其它的跟踪文件,默认是同一目录下
第三个:表示滚动文件的大小(单位MB),当到达这个值就会创建新的滚动文件
第四个:跟踪的停止时间,这里为Null,表示没有固定的停止时间
第五个:当前跟踪的状态:0 停止;1 运行
找到该目录,我们查看下该文件:
、
系统默认提供5个跟踪文件,并且每一个文件默认大小都是20MB,SQL Server会自己维护这5个文件,当实例重启的时候或者到达最大值的时候,之后会重新生成新的文件,将最早的跟踪文件删除,依次滚动更新。
我们通过以下命令来查看跟踪文件中的内容:
默认的跟踪文件,提供的跟踪信息还是很全的,从中我们可以找到登录人,操作信息等,上面的截图只是包含的部分信息。我们可以利用该语句进行自己的加工,然后获得更有用的信息。
--获取跟踪文件中前100行执行内容 SELECT TOP 100 gt.[HostName] ,gt.[ServerName] ,gt.[DatabaseName] ,gt.[SPID] ,gt.[ObjectName] ,gt.[objecttype] [ObjectTypeID] ,sv.[subclass_name] [ObjectType] ,e.[category_id] [CategoryID] ,c.[Name] [Category] ,gt.[EventClass] [EventID] ,e.[Name] [EventName] ,gt.[LoginName] ,gt.[ApplicationName] ,gt.[StartTime] ,gt.[TextData] FROM fn_trace_gettable('E:\dataDefaultFileManger\MSSQL10.MSSQLSERVER\MSSQL\Log\log_1267.trc', DEFAULT) gt LEFT JOIN sys.trace_subclass_values sv ON gt.[eventclass] = sv.[trace_event_id] AND sv.[subclass_value] = gt.[objecttype] INNER JOIN sys.trace_events e ON gt.[eventclass] = e.[trace_event_id] INNER JOIN sys.trace_categories c ON e.[category_id] = c.[category_id] WHERE gt.[spid] > 50 AND --50以内的spid为系统使用 gt.[DatabaseName] = 'master' AND --根据DatabaseName过滤 gt.[ObjectName] = 'fn_trace_getinfo' AND --根据objectname过滤 e.[category_id] = 5 AND --category 5表示对象,8表示安全 e.[trace_event_id] = 46 --trace_event_id --46表示Create对象(Object:Created), --47表示Drop对象(Object:Deleted), --93表示日志文件自动增长(Log File Auto Grow), --164表示Alter对象(Object:Altered), --20表示错误日志(Audit Login Failed) ORDER BY [StartTime] DESC
我创建了一张表,通过上面的跟踪,可以跟踪到该记录的信息,根据不同的过滤信息,我们可以查询出到跟踪的某个库的某个表的更改信息,包括:46创建(Created)、47删除(Deleted)、93文件自动增长信息(Log File Auto Grow)、146修改(Alter)、20表示错误日志(Login Failed)
在生产环境中,以上几个分类都是比较常用的,对定位部分问题的定位能够在找到充分的证据可循,比如某厮将数据库数据删除掉了还不承认等,这里面的Login Failed信息,能够追踪出有那么用户尝试登陆过数据库,并且失败,如果大面积的出现这种情况,那就要谨防黑客袭击了。
当然,这里我还可以利用SQL Server自带的Profile工具,打开查看跟踪文件中的内容。
这个图像化的工具就比较熟悉了,直接打开进行筛选就可以了。
这种方式看似不错,但是它也有本身的缺点,我们来看:
1、这5个文件是滚动更新的,而且每个文件默认最大都为20MB,并且没有提供更改的接口,所以当文件填充完之后就会删除掉,所以会找不到太久以前的内容;
2、本身默认的跟踪,只是提供一些关键信息的追踪,其中包括:auditing events,database events,error events,full text events,object creation,object deletion,object alteration,想要找到其它更详细的内容,此方式可能无能为力;
3、在SQL Server2012后续版本的 Microsoft SQL Server 将删除该功能,改用扩展事件。
二.自定义跟踪信息(Default Trace)
根据上面SQL Server自带的跟踪信息有一些局限性,SQL Server为我们提供了自定义跟踪的接口,我们可以自己定义跟踪,充分扩展方法。
利用如下系统存储过程,我们可以创建自定义的Trace
sp_trace_create [ @traceid = ] trace_id OUTPUT , [ @options = ] option_value , [ @tracefile = ] 'trace_file' [ , [ @maxfilesize = ] max_file_size ] [ , [ @stoptime = ] 'stop_time' ] [ , [ @filecount = ] 'max_rollover_files' ]
@traceid 系统默认分配跟踪的ID号
@options 指定为跟踪设置的选项,系统默认提供的几个选项:
2表示当文件写满的时候,关闭当前跟踪并创建新文件。
4表示如果不能将跟踪写入文件,不管什么原因导致,SQL Server则会关闭。这个可以利用此选项,追踪问题
8制定服务器产生的最后5MB的跟踪信息记录由服务器保存。
@tracefile 跟踪文件的路径,这里可以是share的路径
@maxfilesize 跟踪文件的大小,单位是MB,默认不设置为5MB
@stoptime 跟踪停止的时间,利用它我们可以定时跟踪结束的日期
@filecount 默认生产的跟踪文件的数量,比如默认的为5个,那就在第5个文件写完的时候进行覆盖第1个文件滚动
比如我们可以利用如下脚本进行创建
--创建跟踪文件返回值 declare @rc int --创建一个跟踪句柄 declare @TraceID int --创建跟踪文件路径 declare @TraceFilePath nvarchar(500) set @TraceFilePath=N'F:\SQLTest\' --跟踪文件的大小 declare @maxfilesize bigint set @maxfilesize=5 --设置停止的时间 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) select @TraceID
我们通过上面的跟踪创建的过程,可以在系统自带的默认的sys.traces中找到该跟踪的明细
select * from sys.traces where id=2
通过上面的脚本,我们已经创建了一个新的跟踪(trace),但是这个跟踪状态为0,也就是说还没有运行,下面我们的步骤就是要为这个跟踪添加事件(event)
这个也是利用SQL Server为我们提供的操作函数
sp_trace_setevent [ @traceid = ] trace_id , [ @eventid = ] event_id , [ @columnid = ] column_id , [ @on = ] on
@traceid 要修改的跟踪的 ID号
@eventid 要打开的事件的 ID
@columnid 要为该事件添加的列的 ID
@on 表示事件状态
其中最主要的就是时间ID,这个是SQL Server为我们提供的一些列的码表时间值,具体值可以参考联机丛书 sp_trace_setevent (Transact-SQL)
这里面最常用的就是:
事件号 |
事件名称 |
说明 |
---|---|---|
10 |
RPC:Completed |
在完成了远程过程调用 (RPC) 时发生。 |
11 |
RPC:Starting |
在启动了 RPC 时发生。 |
12 |
SQL:BatchCompleted |
在完成了 Transact-SQL 批处理时发生。 |
13 |
SQL:BatchStarting |
在启动了 Transact-SQL 批处理时发生。 |
14 |
Audit Login |
在用户成功登录到 SQL Server 时发生。 |
15 |
Audit Logout |
在用户从 SQL Server 注销时发生。 |
16 |
Attention |
在发生需要关注的事件(如客户端中断请求或客户端连接中断)时发生。 |
17 |
ExistingConnection |
检测在启动跟踪前连接到 SQL Server 的用户的所有活动。 |
18 |
Audit Server Starts and Stops |
在修改 SQL Server 服务状态时发生。 |
20 |
Audit Login Failed |
指示试图从客户端登录到 SQL Server 失败。 |
21 |
EventLog |
指示已将事件记录到 Windows 应用程序日志中。 |
22 |
ErrorLog |
指示已将错误事件记录到 SQL Server 错误日志中。 |
23 |
Lock:Released |
指示已释放某个资源(如页)的锁。 |
24 |
Lock:Acquired |
指示获取了某个资源(如数据页)的锁。 |
25 |
Lock:Deadlock |
指示两个并发事务由于试图获得对方事务拥有的资源的不兼容锁而发生了相互死锁。 |
26 |
Lock:Cancel |
指示已取消获取资源锁(例如,由于死锁)。 |
27 |
Lock:Timeout |
指示由于其他事务持有所需资源的阻塞锁而使对资源(例如页)锁的请求超时。 超时由 @@LOCK_TIMEOUT 函数确定,并可用 SET LOCK_TIMEOUT 语句设置。 |
28 |
Degree of Parallelism Event(7.0 插入) |
在执行 SELECT、INSERT 或 UPDATE 语句之前发生。 |
33 |
Exception |
指示 SQL Server 中出现了异常。 |
34 |
SP:CacheMiss |
指示未在过程缓存中找到某个存储过程。 |
35 |
SP:CacheInsert |
指示某个项被插入到过程缓存中。 |
36 |
SP:CacheRemove |
指示从过程缓存中删除了某个项。 |
37 |
SP:Recompile |
指示已重新编译存储过程。 |
38 |
SP:CacheHit |
指示在过程缓存中找到了存储过程。 |
40 |
SQL:StmtStarting |
在启动了 Transact-SQL 语句时发生。 |
41 |
SQL:StmtCompleted |
在完成了 Transact-SQL 语句时发生。 |
42 |
SP:Starting |
指示启动了存储过程。 |
43 |
SP:Completed |
指示完成了存储过程。 |
44 |
SP:StmtStarting |
指示已开始执行存储过程中的 Transact-SQL 语句。 |
45 |
SP:StmtCompleted |
指示存储过程中的 Transact-SQL 语句已执行完毕。 |
46 |
Object:Created |
指示 CREATE INDEX、CREATE TABLE 和 CREATE DATABASE 这样的语句已创建了一个对象。 |
47 |
Object:Deleted |
指示已在 DROP INDEX 和 DROP TABLE 这样的语句中删除了对象。 |
50 |
SQL Transaction |
跟踪 Transact-SQL BEGIN、COMMIT、SAVE 和 ROLLBACK TRANSACTION 语句。 |
51 |
Scan:Started |
指示启动了表或索引扫描 |
52 |
Scan:Stopped |
指示停止了表或索引扫描。 |
53 |
CursorOpen |
指示 ODBC、OLE DB 或 DB-Library 在 Transact-SQL 语句中打开了一个游标。 |
54 |
TransactionLog |
将事务写入事务日志时进行跟踪。 |
55 |
Hash Warning |
指示未在缓冲分区进行的某一哈希操作(例如,哈希联接、哈希聚合、哈希 union 运算、哈希非重复)已恢复为替换计划。 发生此事件的原因可能是递归深度、数据扭曲、跟踪标记或位计数。 |
58 |
Auto Stats |
指示发生了自动更新索引统计信息。 |
59 |
Lock:Deadlock Chain |
为导致死锁的每个事件而生成。 |
60 |
Lock:Escalation |
指示较细粒度的锁转换成了较粗粒度的锁(例如,页锁升级或转换为 TABLE 或 HoBT 锁)。 |
61 |
OLE DB Errors |
指示发生了 OLE DB 错误。 |
67 |
Execution Warnings |
指示在执行 SQL Server 语句或存储过程期间发生的任何警告。 |
68 |
Showplan Text (Unencoded) |
显示所执行 Transact-SQL 语句的计划树。 |
69 |
Sort Warnings |
指示不适合内存的排序操作。 不包括与创建索引有关的排序操作;只包括某查询内的排序操作(如 SELECT 语句中使用的 ORDER BY 子句)。 |
70 |
CursorPrepare |
指示已准备了 ODBC、OLE DB 或 DB-Library 用于 Transact-SQL 语句的游标。 |
71 |
Prepare SQL |
ODBC、OLE DB 或 DB-Library 已准备好了一个或多个要使用的 Transact-SQL 语句。 |
72 |
Exec Prepared SQL |
ODBC、OLE DB 或 DB-Library 已执行了一个或多个准备好的 Transact-SQL 语句。 |
73 |
Unprepare SQL |
ODBC、OLE DB 或 DB-Library 已撤消(删除)了一个或多个准备好的 Transact-SQL 语句。 |
74 |
CursorExecute |
执行了先前由 ODBC、OLE DB 或 DB-Library 为 Transact-SQL 语句准备的游标。 |
75 |
CursorRecompile |
由 ODBC 或 DB-Library 为 Transact-SQL 语句打开的游标已直接重新编译或由于架构更改而重新编译。 为 ANSI 和非 ANSI 游标触发。 |
76 |
CursorImplicitConversion |
SQL Server 将 Transact-SQL 语句的游标从一种类型转换为另一种类型。 为 ANSI 和非 ANSI 游标触发。 |
77 |
CursorUnprepare |
ODBC、OLE DB 或 DB-Library 撤消(删除)了准备好的 Transact-SQL 语句的游标。 |
78 |
CursorClose |
关闭了先前由 ODBC、OLE DB 或 DB-Library 为 Transact-SQL 语句打开的游标。 |
79 |
Missing Column Statistics |
可能曾经对优化器有用的列统计信息不可用。 |
80 |
Missing Join Predicate |
正在执行没有联接谓词的查询。 这可能导致长时间运行查询。 |
81 |
Server Memory Change |
SQL Server 内存的使用量已增加或减少了 1 MB 或最大服务器内存的 5%(两者中较大者)。 |
82-91 |
User Configurable (0-9) |
用户定义的事件数据。 |
92 |
Data File Auto Grow |
指示服务器已自动扩展了数据文件。 |
93 |
Log File Auto Grow |
指示服务器已自动扩展了日志文件。 |
94 |
Data File Auto Shrink |
指示服务器已自动收缩了数据文件。 |
95 |
Log File Auto Shrink |
指示服务器已自动收缩了日志文件。 |
96 |
Showplan Text |
显示来自查询优化器的 SQL 语句的查询计划树。 请注意,TextData 列不包含此事件的显示计划。 |
97 |
Showplan All |
显示查询计划,并显示已执行的 SQL 语句的完整编译时详细信息。 请注意,TextData 列不包含此事件的显示计划。 |
98 |
Showplan Statistics Profile |
显示查询计划,并显示已执行的 SQL 语句的完整运行时详细信息。 请注意,TextData 列不包含此事件的显示计划。 |
100 |
RPC Output Parameter |
生成每个 RPC 的参数的输出值。 |
108 |
Audit Add Login to Server Role Event |
在从固定服务器角色添加或删除登录时发生;针对 sp_addsrvrolemember 和 sp_dropsrvrolemember。 |
112 |
Audit App Role Change Password Event |
在更改应用程序角色的密码时发生。 |
113 |
Audit Statement Permission Event |
在使用语句权限(如 CREATE TABLE)时发生。 |
114 |
Audit Schema Object Access Event |
在成功或未成功使用了对象权限(如 SELECT)时发生。 |
115 |
Audit Backup/Restore Event |
在发出 BACKUP 或 RESTORE 命令时发生。 |
116 |
Audit DBCC Event |
在发出 DBCC 命令时发生。 |
117 |
Audit Change Audit Event |
在修改审核跟踪时发生。 |
118 |
Audit Object Derived Permission Event |
在发出 CREATE、ALTER 和 DROP 对象命令时发生。 |
119 |
OLEDB Call Event |
为分布式查询和远程存储过程调用 OLE DB 访问接口时发生。 |
120 |
OLEDB QueryInterface Event |
为分布式查询和远程存储过程调用 OLE DB QueryInterface 时发生。 |
121 |
OLEDB DataRead Event |
对 OLE DB 访问接口调用数据请求时发生。 |
122 |
Showplan XML |
在执行 SQL 语句时发生。 包括该事件可以标识 Showplan 运算符。 每个事件都存储在格式正确的 XML 文档中。 请注意,此事件的Binary 列包含已编码的显示计划。 使用 SQL Server Profiler 可打开跟踪并查看显示计划。 |
123 |
SQL:FullTextQuery |
执行全文查询时发生。 |
124 |
Broker:Conversation |
报告 Service Broker 会话的进度。 |
125 |
Deprecation Announcement |
使用将从 SQL Server 的未来版本中删除的功能时发生。 |
126 |
Deprecation Final Support |
使用将从 SQL Server 的下一个主版本中删除的功能时发生。 |
127 |
Exchange Spill Event |
在 tempdb 数据库临时写入并行查询计划中的通信缓冲区时发生。 |
128 |
Audit Database Management Event |
创建、更改或删除数据库时发生。 |
129 |
Audit Database Object Management Event |
对数据库对象(如架构)执行 CREATE、ALTER 或 DROP 语句时发生。 |
130 |
Audit Database Principal Management Event |
创建、更改或删除数据库的主体(如用户)时发生。 |
131 |
Audit Schema Object Management Event |
创建、更改或删除服务器对象时发生。 |
132 |
Audit Server Principal Impersonation Event |
服务器范围中发生模拟(如 EXECUTE AS LOGIN)时发生。 |
133 |
Audit Database Principal Impersonation Event |
数据库范围中发生模拟(如 EXECUTE AS USER 或 SETUSER)时发生。 |
134 |
Audit Server Object Take Ownership Event |
服务器范围中的对象的所有者发生更改时发生。 |
135 |
Audit Database Object Take Ownership Event |
数据库范围中的对象的所有者发生更改时发生。 |
136 |
Broker:Conversation Group |
Service Broker 创建新的会话组或删除现有会话组时发生。 |
137 |
Blocked Process Report |
进程被阻塞的时间超过了指定的时间时发生。 不包括系统进程或正在等待未发现死锁的资源的进程。 请使用 sp_configure 来配置生成报表时的阈值和频率。 |
138 |
Broker:Connection |
报告 Service Broker 管理的传输连接的状态。 |
139 |
Broker:Forwarded Message Sent |
Service Broker 转发消息时发生。 |
140 |
Broker:Forwarded Message Dropped |
Service Broker 删除用于转发的消息时发生。 |
141 |
Broker:Message Classify |
Service Broker 确定消息的路由时发生。 |
142 |
Broker:Transmission |
指示在 Service Broker 传输层中发生了错误。 错误号和状态值指示了错误源。 |
143 |
Broker:Queue Disabled |
指示检测到有害消息,这是由于在 Service Broker 队列中有五个连续的事务回滚。 该事件包含数据库 ID 和包含有害消息的队列的队列 ID。 |
146 |
Showplan XML Statistics Profile |
在执行 SQL 语句时发生。 标识 Showplan 运算符,并显示完整的编译时数据。 请注意,此事件的 Binary 列包含已编码的显示计划。 使用 SQL Server Profiler 可打开跟踪并查看显示计划。 |
148 |
Deadlock Graph |
取消获取锁的尝试时发生,这是因为该尝试是死锁的一部分,并且被选为死锁牺牲品。 提供死锁的 XML 说明。 |
149 |
Broker:Remote Message Acknowledgement |
Service Broker 发送或收到消息确认时发生。 |
150 |
Trace File Close |
跟踪文件在回滚期间关闭时发生。 |
152 |
Audit Change Database Owner |
使用 ALTER AUTHORIZATION 更改数据库的所有者,并且检查执行该操作的权限时发生。 |
153 |
Audit Schema Object Take Ownership Event |
使用 ALTER AUTHORIZATION 来将所有者分配给对象,并且检查执行该操作的权限时发生。 |
155 |
FT:Crawl Started |
全文爬网(填充)开始时发生。 用于检查工作线程任务是否拾取了爬网请求。 |
156 |
FT:Crawl Stopped |
全文爬网(填充)停止时发生。 爬网成功完成或发生错误时停止。 |
157 |
FT:Crawl Aborted |
在全文爬网过程中遇到异常时发生。 通常导致全文爬网停止。 |
158 |
Audit Broker Conversation |
报告与 Service Broker 对话安全性相关的审核消息。 |
159 |
Audit Broker Login |
报告与 Service Broker 传输安全性相关的审核消息。 |
160 |
Broker:Message Undeliverable |
Service Broker 无法保留收到的消息时发生,该消息应当已传递给某个服务。 |
161 |
Broker:Corrupted Message |
Service Broker 收到损坏的消息时发生。 |
162 |
User Error Message |
显示出现错误或异常时用户看到的错误消息。 |
163 |
Broker:Activation |
队列监视器启动激活存储过程时,发送 QUEUE_ACTIVATION 通知时,或者队列监视器启动的激活存储过程退出时发生。 |
164 |
Object:Altered |
数据库对象更改时发生。 |
165 |
Performance statistics |
将经过编译的查询计划第一次缓存、重新编译或从计划缓存中删除时发生。 |
166 |
SQL:StmtRecompile |
发生语句级别的重新编译时发生。 |
167 |
Database Mirroring State Change |
镜像数据库的状态更改时发生。 |
168 |
Showplan XML For Query Compile |
编译 SQL 语句时发生。 显示完整的编译时数据。 请注意,此事件的 Binary 列包含已编码的显示计划。 使用 SQL Server Profiler 可打开跟踪并查看显示计划。 |
169 |
Showplan All For Query Compile |
编译 SQL 语句时发生。 显示完整的编译时数据。 用于标识 Showplan 运算符。 |
170 |
Audit Server Scope GDR Event |
指示在服务器范围中发生了权限的授予、拒绝或撤消事件(如创建登录)。 |
171 |
Audit Server Object GDR Event |
指示发生了对架构对象(如表或函数)的授予、拒绝或撤消事件。 |
172 |
Audit Database Object GDR Event |
指示发生了对数据库对象(如程序集和架构)的授予、拒绝或撤消事件。 |
173 |
Audit Server Operation Event |
使用了安全审核操作(如使用了更改设置、资源、外部访问或授权)时发生。 |
175 |
Audit Server Alter Trace Event |
检查语句的 ALTER TRACE 权限时发生。 |
176 |
Audit Server Object Management Event |
创建、更改或删除服务器对象时发生。 |
177 |
Audit Server Principal Management Event |
创建、更改或删除了服务器主体时发生。 |
178 |
Audit Database Operation Event |
发生数据库操作(如检查或订阅查询通知)时发生。 |
180 |
Audit Database Object Access Event |
访问数据库对象(如架构)时发生。 |
181 |
TM: Begin Tran starting |
BEGIN TRANSACTION 请求开始时发生。 |
182 |
TM: Begin Tran completed |
BEGIN TRANSACTION 请求完成时发生。 |
183 |
TM: Promote Tran starting |
PROMOTE TRANSACTION 请求开始时发生。 |
184 |
TM: Promote Tran completed |
PROMOTE TRANSACTION 请求完成时发生。 |
185 |
TM: Commit Tran starting |
COMMIT TRANSACTION 请求开始时发生。 |
186 |
TM: Commit Tran completed |
COMMIT TRANSACTION 请求完成时发生。 |
187 |
TM: Rollback Tran starting |
ROLLBACK TRANSACTION 请求开始时发生。 |
188 |
TM: Rollback Tran completed |
ROLLBACK TRANSACTION 请求完成时发生。 |
189 |
Lock:Timeout (timeout > 0) |
对资源(如页)的锁请求超时时发生。 |
190 |
Progress Report: Online Index Operation |
报告生成进程正在运行时,联机索引生成操作的进度。 |
191 |
TM: Save Tran starting |
SAVE TRANSACTION 请求开始时发生。 |
192 |
TM: Save Tran completed |
SAVE TRANSACTION 请求完成时发生。 |
193 |
Background Job Error |
后台作业不正常终止时发生。 |
194 |
OLEDB Provider Information |
分布式查询运行并收集对应于提供程序连接的信息时发生。 |
195 |
Mount Tape |
收到磁带装入请求时发生。 |
196 |
Assembly Load |
发生加载 CLR 程序集的请求时发生。 |
198 |
XQuery Static Type |
执行 XQuery 表达式时发生。 此事件类提供静态类型的 XQuery 表达式。 |
199 |
QN: subscription |
无法订阅查询注册时发生。 TextData 列包含事件的有关信息。 |
200 |
QN: parameter table |
有关活动订阅的信息存储在内部参数表中。 在创建或删除参数表时发生该事件类。 通常,重新启动数据库时将创建或删除这些表。TextData 列包含事件的有关信息。 |
201 |
QN: template |
查询模板代表订阅查询的类。 通常,除参数值以外,相同类中的查询是相同的。 当新的订阅请求针对已存在的类 (Match)、新类 (Create) 或 Drop 类(指示清除没有活动订阅的查询类的模板)时,发生此事件类。 TextData 列包含事件的有关信息。 |
202 |
QN: dynamics |
跟踪查询通知的内部活动。 TextData 列包含事件的有关信息。 |
213 |
Database Suspect Data Page |
指示何时将某页添加到 msdb 的 suspect_pages 表。 |
214 |
CPU threshold exceeded |
指示资源调控器检测到查询超过 CPU 阈值 (REQUEST_MAX_CPU_TIME_SEC) 的时间。 |
215 |
指示 LOGON 触发器或资源调控器分类器函数开始执行的时间。 |
指示 LOGON 触发器或资源调控器分类器函数开始执行的时间。 |
216 |
PreConnect:Completed |
指示 LOGON 触发器或资源调控器分类器函数完成执行的时间。 |
217 |
Plan Guide Successful |
指示 SQL Server 已成功为计划指南中包含的查询或批处理生成执行计划。 |
218 |
Plan Guide Unsuccessful |
指示 SQL Server 无法为包含计划指南的查询或批处理生成执行计划。 SQL Server 尝试在不应用计划指南的情况下为此查询或批处理生成执行计划。 无效的计划指南可能是导致此问题的原因。 您可以通过使用 sys.fn_validate_plan_guide 系统函数验证该计划指南。 |
上述的跟踪事件中,基本包含了SQL Server中所能做的任何操作,我们可以根据自己需要进行定义,当我们可以针对日常经常遇到的一些问题进行定位,比如:死锁、等待、登录失败等等吧...当然也可以追踪某个人的所有行为,这里我们来定义几个来看看
我们定义追踪所有语句批量操作的追踪,从上面表我们可以查找到为12,13
exec sp_trace_setevent 2,12,1,1 exec sp_trace_setevent 2,13,1,1
通过如下存储过程,将我们自定的追踪启动
--设置跟踪状态以启动 exec sp_trace_setstatus @TraceID,1
至此,我们新建的追踪已经开始运行了,我们可以利用上面的方法,来查看我们生成的追踪文件了,其实大部分时候,我们都是利用此种方法设置好”圈套“,等待鱼儿上网
比如死锁查找,CPU消耗高,IO值高的那些语句....
我们可以利用如下语句,查找跟踪文件的信息
--查看跟踪文件以表显示 select * from ::fn_trace_gettable('F:\SQLTest\.trc',1)
将我们刚才的所有操作,已经追踪出来了。
通过如下命令进行跟踪的关闭
--设置跟踪状态以停止 exec sp_trace_setstatus @TraceID,0
通过如下命令进行跟踪的删除
--从系统中移除跟踪 exec sp_trace_setstatus @TraceID,2
我们知道在SQL Server默认的跟踪文件在实例重启时候,都会消失,所以我们可以通过如下方法解决,保证在每次实例重新启动的时候都会执行该追踪
--新建追踪的存储过程 use master go create proc StartBlackBoxTrace as begin --默认开启追踪所有的SQL 执行语句,文件文件路径为默认 DECLARE @TraceID int DECLARE @MaxFileSize bigint SET @MaxFileSize=25 EXEC SP_TRACE_CREATE @TraceID OUTPUT, 8, NULL, @MaxFileSize EXEC SP_TRACE_SETSTATUS @TraceID,1 END --将该存储过程设置为SQL Server服务启动时自动启动 EXEC sp_procoption 'StartBlackBoxTrace','STARTUP','ON' GO
通过如下脚本删除到所有的跟踪
create proc [dbo].[Performance_Trace_StopAll] AS declare traceCursor cursor for select id from sys.traces where id <> 1 open traceCursor declare @curid int fetch next from traceCursor into @curid while(@@fetch_status=0) begin exec sp_trace_setstatus @curid,0 exec sp_trace_setstatus @curid,2 fetch next from traceCursor into @curid end close traceCursor deallocate traceCursor
三.死锁案例(2014年11月23日晚补充)
这里我们来利用自己新建跟踪来跟踪一个死锁的发生过程,并且将其记录到我们的Trace文件中,这里我们来制作一个死锁
以上代码参照院子里大牛宋沄剑,这里我们利用系统的自带的profile进行设计追踪,我们直接选择系统自带的死锁模板,进行追踪
然后设置,默认的SPID为大于等于50,小于50的为系统自有事件
然后,我们利用上面的死锁脚本,运行获取死锁的捕捉
可以看到,我们已经顺利的追踪到这个死锁。我们知道这种追踪是高成本的,并且我们有时候不知道死锁发生的具体时间,所以不能一直开着这个Profile,出于性能考虑也不建议这么做,所以我们采用新建的Trace文件,来保存改脚本,然后重定向到我们自己的文件夹,将死锁的信息放置到该文件夹下,提供更大灵活性。
SQL Server本身自带的Profile工具就提供编辑脚本的功能,我们将上面的设计,导出成Trace脚本,我们点击“文件”,导出该设计脚本
将该脚本保存到一个位置,然后我们打开,我顺便将默认的文件路径添加上
/****************************************************/ /* Created by: SQL Server 2008 Profiler */ /* Date: 2014/11/23 20:28:11 */ /****************************************************/ -- Create a Queue declare @rc int declare @TraceID int declare @maxfilesize bigint --可以更改文件大小 set @maxfilesize = 5 --默认死锁文件放置目录 declare @FilePath nvarchar(max) set @FilePath=N'F:\SQLTest\DeadLock.trc' -- Please replace the text InsertFileNameHere, with an appropriate -- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension -- will be appended to the filename automatically. If you are writing from -- remote server to local drive, please use UNC path and make sure server has -- write access to your network share exec @rc = sp_trace_create @TraceID output, 0, @FilePath, @maxfilesize, NULL if (@rc != 0) goto error -- Client side File and Table cannot be scripted -- Set the events declare @on bit set @on = 1 exec sp_trace_setevent @TraceID, 137, 15, @on exec sp_trace_setevent @TraceID, 137, 32, @on exec sp_trace_setevent @TraceID, 137, 1, @on exec sp_trace_setevent @TraceID, 137, 13, @on exec sp_trace_setevent @TraceID, 137, 22, @on exec sp_trace_setevent @TraceID, 148, 11, @on exec sp_trace_setevent @TraceID, 148, 12, @on exec sp_trace_setevent @TraceID, 148, 14, @on exec sp_trace_setevent @TraceID, 148, 1, @on exec sp_trace_setevent @TraceID, 26, 15, @on exec sp_trace_setevent @TraceID, 26, 32, @on exec sp_trace_setevent @TraceID, 26, 1, @on exec sp_trace_setevent @TraceID, 26, 9, @on exec sp_trace_setevent @TraceID, 26, 57, @on exec sp_trace_setevent @TraceID, 26, 2, @on exec sp_trace_setevent @TraceID, 26, 10, @on exec sp_trace_setevent @TraceID, 26, 11, @on exec sp_trace_setevent @TraceID, 26, 35, @on exec sp_trace_setevent @TraceID, 26, 12, @on exec sp_trace_setevent @TraceID, 26, 13, @on exec sp_trace_setevent @TraceID, 26, 6, @on exec sp_trace_setevent @TraceID, 26, 14, @on exec sp_trace_setevent @TraceID, 26, 22, @on exec sp_trace_setevent @TraceID, 25, 15, @on exec sp_trace_setevent @TraceID, 25, 32, @on exec sp_trace_setevent @TraceID, 25, 1, @on exec sp_trace_setevent @TraceID, 25, 9, @on exec sp_trace_setevent @TraceID, 25, 57, @on exec sp_trace_setevent @TraceID, 25, 2, @on exec sp_trace_setevent @TraceID, 25, 10, @on exec sp_trace_setevent @TraceID, 25, 11, @on exec sp_trace_setevent @TraceID, 25, 35, @on exec sp_trace_setevent @TraceID, 25, 12, @on exec sp_trace_setevent @TraceID, 25, 13, @on exec sp_trace_setevent @TraceID, 25, 6, @on exec sp_trace_setevent @TraceID, 25, 14, @on exec sp_trace_setevent @TraceID, 25, 22, @on exec sp_trace_setevent @TraceID, 59, 32, @on exec sp_trace_setevent @TraceID, 59, 1, @on exec sp_trace_setevent @TraceID, 59, 57, @on exec sp_trace_setevent @TraceID, 59, 2, @on exec sp_trace_setevent @TraceID, 59, 14, @on exec sp_trace_setevent @TraceID, 59, 22, @on exec sp_trace_setevent @TraceID, 59, 35, @on exec sp_trace_setevent @TraceID, 59, 12, @on exec sp_trace_setevent @TraceID, 60, 32, @on exec sp_trace_setevent @TraceID, 60, 9, @on exec sp_trace_setevent @TraceID, 60, 57, @on exec sp_trace_setevent @TraceID, 60, 10, @on exec sp_trace_setevent @TraceID, 60, 11, @on exec sp_trace_setevent @TraceID, 60, 35, @on exec sp_trace_setevent @TraceID, 60, 12, @on exec sp_trace_setevent @TraceID, 60, 6, @on exec sp_trace_setevent @TraceID, 60, 14, @on exec sp_trace_setevent @TraceID, 60, 22, @on exec sp_trace_setevent @TraceID, 189, 15, @on exec sp_trace_setevent @TraceID, 189, 32, @on exec sp_trace_setevent @TraceID, 189, 1, @on exec sp_trace_setevent @TraceID, 189, 9, @on exec sp_trace_setevent @TraceID, 189, 57, @on exec sp_trace_setevent @TraceID, 189, 2, @on exec sp_trace_setevent @TraceID, 189, 10, @on exec sp_trace_setevent @TraceID, 189, 11, @on exec sp_trace_setevent @TraceID, 189, 35, @on exec sp_trace_setevent @TraceID, 189, 12, @on exec sp_trace_setevent @TraceID, 189, 13, @on exec sp_trace_setevent @TraceID, 189, 6, @on exec sp_trace_setevent @TraceID, 189, 14, @on exec sp_trace_setevent @TraceID, 189, 22, @on exec sp_trace_setevent @TraceID, 45, 16, @on exec sp_trace_setevent @TraceID, 45, 1, @on exec sp_trace_setevent @TraceID, 45, 9, @on exec sp_trace_setevent @TraceID, 45, 17, @on exec sp_trace_setevent @TraceID, 45, 10, @on exec sp_trace_setevent @TraceID, 45, 18, @on exec sp_trace_setevent @TraceID, 45, 11, @on exec sp_trace_setevent @TraceID, 45, 35, @on exec sp_trace_setevent @TraceID, 45, 12, @on exec sp_trace_setevent @TraceID, 45, 13, @on exec sp_trace_setevent @TraceID, 45, 6, @on exec sp_trace_setevent @TraceID, 45, 14, @on exec sp_trace_setevent @TraceID, 45, 22, @on exec sp_trace_setevent @TraceID, 45, 15, @on exec sp_trace_setevent @TraceID, 44, 1, @on exec sp_trace_setevent @TraceID, 44, 9, @on exec sp_trace_setevent @TraceID, 44, 10, @on exec sp_trace_setevent @TraceID, 44, 11, @on exec sp_trace_setevent @TraceID, 44, 35, @on exec sp_trace_setevent @TraceID, 44, 12, @on exec sp_trace_setevent @TraceID, 44, 6, @on exec sp_trace_setevent @TraceID, 44, 14, @on exec sp_trace_setevent @TraceID, 44, 22, @on exec sp_trace_setevent @TraceID, 41, 15, @on exec sp_trace_setevent @TraceID, 41, 16, @on exec sp_trace_setevent @TraceID, 41, 1, @on exec sp_trace_setevent @TraceID, 41, 9, @on exec sp_trace_setevent @TraceID, 41, 17, @on exec sp_trace_setevent @TraceID, 41, 10, @on exec sp_trace_setevent @TraceID, 41, 18, @on exec sp_trace_setevent @TraceID, 41, 11, @on exec sp_trace_setevent @TraceID, 41, 35, @on exec sp_trace_setevent @TraceID, 41, 12, @on exec sp_trace_setevent @TraceID, 41, 13, @on exec sp_trace_setevent @TraceID, 41, 6, @on exec sp_trace_setevent @TraceID, 41, 14, @on exec sp_trace_setevent @TraceID, 40, 1, @on exec sp_trace_setevent @TraceID, 40, 9, @on exec sp_trace_setevent @TraceID, 40, 6, @on exec sp_trace_setevent @TraceID, 40, 10, @on exec sp_trace_setevent @TraceID, 40, 14, @on exec sp_trace_setevent @TraceID, 40, 11, @on exec sp_trace_setevent @TraceID, 40, 35, @on exec sp_trace_setevent @TraceID, 40, 12, @on -- Set the Filters declare @intfilter int declare @bigintfilter bigint exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - efbc9d24-69cd-465f-8daf-e38493da0332' -- 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
我们只需要将这个脚本运行就可以,当然此段脚本,在实例重启的时候,所有的trace都会删除掉,可以将该段代码改成存储过程,然后设置成实例启动的时候运行,
方法参考本篇的上半部分。
当然我们也可以设置别的参数,比如启动时间,运行时间,跟踪文件大小,位置,数量等,删除不必要的trace跟踪事件...
当运行到一段时间之后,我们直接拷贝下来,找台电脑分析就可以了。
更灵活的方式是采取非业务高峰期,利用SQL Server自带的邮件提醒功能,直接检测出问题,然后Send Email....
四.SQL Server中黑匣子(2014年11月28日晚补充)
黑匣子作为飞机出现事故后的追踪利器,在微软的SQL Server这个数据库中默认也给装上了此引擎,但是没有开启,此功能可能帮助我们诊断间歇性的服务器崩溃。它比我们上面介绍的默认开启的跟踪跟踪的信息更全,跟踪更大一些。其内容包含了:“SP:启动”、“SQL:批处理启动”、异常和注意等事件
这个跟踪通过在sp_trace_create的默认@option参数设置为8来配置的。代码如下:
DECLARE @Traced INT EXEC sp_trace_create @Traced OUTPUT, @options=8 EXEC sp_trace_setstatus @Traced,1
通过以上的配置会自动配置成两个滚动文件,当达到默认的最大文件容量5MB的时候,就在两个文件中循环滚动依次更新。
当然如果感觉生成的文件5MB有点小,可以手动配置更改大小,或者自定义文件路径,这些都是允许自定义设置的。
DECLARE @TraceID INT DECLARE @maxfilesize BIGINT SET @maxfilesize=25 DECLARE @tracefile nvarchar(245) SET @tracefile=N'F:\SQLTest\TRACE\MYTRACE.trc' EXEC sp_trace_create @TraceID OUTPUT, @options=8, @tracefile=NULL, @maxfilesize=@maxfilesize EXEC sp_trace_setstatus @TraceID,1
此方法能将数据库执行的所有SQL滚动记录下来,以防止SQL Server宕机之后的事故查找。
参考我博文的上面介绍,只需要将这个方法存储于存储过程,然后设置成实例启动后运行,那么恭喜你的SQL Server已经成功装上了黑匣子引擎,假如有一天突然宕机,我们只需要打开此黑匣子就可以。
结语
参考文献有下面