SQL Server Default Trace查看是谁对数据库进行了DDL操作
在我们的工作中可能会遇到这样一种情形。由于数据库中某些对象被altered/created/deleted,造成我们的应用程序crash。
当我们把问题解决之后,老板可能会问发生了什么?为什么会这样?是谁干的?
在SQL Server 2005中提供了DDL trigger,它能回答所用这些问题,但我们没有在事前实现这一工作。
在这种情况下,有些人可能会对老板说:“我不知道,但我能去查。”之后就疯狂地寻找第三方的工具来读transaction log(事物日志)。他们可能不知道SQL Server 2005有一个内置的功能能回到所用这些问题。
答案藏在一个运行在后台的跟踪(Default Trace)中,我们可以利用Default Trace来寻找排除故障。Default Trace默认会自动运行,因为其非常轻量级,所以不会给系统造成很多负担。对我来说,Default Trace所带来的益处远大于它给数据库带来的负担。Default Trace不能代替DDL trigger的功能,它应被用作SQL实例的监视器,或用来快速获得SQL问题事件的详细信息。
Default Trace不会跟踪所有的事件,它扑捉一些关键性信息,包括auditing events,database events,error events,full text events,object creation,object deletion,object alteration。在本文中我们会关注对象级(event level)的事件,这能回答老板的“是谁干的”这一问题。
下面的代码能查询Default Trace是否已经被开启了
select * from sys.configurations where configuration_id = 1568
如果Default Trace功能没有打开,我们先要打开这个Trace。可以是使用如下代码,注意运行这些代码需要相应的权限。
sp_configure 'show advanced options', 1; go reconfigure; go sp_configure 'default trace enabled', 1; go reconfigure; go
下一段代码是来获取当前跟踪文件的路径。在相应的文件夹下可能会有多个以log_开头的trace文件,我们可以把这些文件载入一个表中,进行分析,但载入多个文件会增加开销。最好只是载入需要分析的信息。
select * from ::fn_trace_getinfo(0)
现在让我们看一看跟踪下来的数据。我们先从创建一个新数据库开始。
use master go create database TraceDB
使用如下的代码来打开跟踪文件,获取创建数据库的信息。这里使用了一些限制性的条件来缩小结果集。
注意:你的跟踪文件路径和名称可能和我的不同。要使用你自己的跟踪路径。
select loginname, loginsid, spid, hostname, applicationname, servername, databasename, objectname, e.category_id, cat.name as [CategoryName], textdata, starttime, eventclass, eventsubclass, --0表示begin,1表示commit e.name as EventName from ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc',0) inner join sys.trace_events e on eventclass = trace_event_id inner join sys.trace_categories as cat on e.category_id = cat.category_id where databasename = 'TraceDB' and objectname is null and --根据objectname来过滤 e.category_id = 5 and --category 5表示对象 e.trace_event_id = 46 --trace_event_id: 46表示Create对象,47表示Drop对象,164表示修改对象
你能看到超过一条的结果,这是因为捕捉到了2事件子类,begin和commit。
现在创建另一个对象并再次进行查询。这次我们创建一张表“MyTable”。
use TraceDB go create table dbo.MyTable( id int identity(1,1) not null, sometext char(3) null ) on primary
现在使用类似的脚本来查询Default Trace,对where条件部分进行修改。
where databasename = 'TraceDB' and objectname = 'MyTable' and e.category_id = 5 and e.trace_event_id = 46
我们同样可以修改对象,代码如下:
use TraceDB go alter table MyTable add col int
使用如下的条件来过滤查询,特别注意修改trace_event_id为164,因为164代表对象修改事件
where databasename = 'TraceDB' and objectname = 'MyTable' and e.category_id = 5 and e.trace_event_id = 164
最后我们可以把"MyTable"Drop掉。
use TraceDB go drop table MyTable
使用如下的条件来过滤查询,注意修改trace_event_id为47,因为47代表对象删除事件
where databasename = 'TraceDB' and objectname = 'MyTable' and e.category_id = 5 and e.trace_event_id = 47
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 基于Microsoft.Extensions.AI核心库实现RAG应用
· Linux系列:如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列:如何用 C#调用 C方法造成内存泄露
· 震惊!C++程序真的从main开始吗?99%的程序员都答错了
· 【硬核科普】Trae如何「偷看」你的代码?零基础破解AI编程运行原理
· 单元测试从入门到精通
· 上周热点回顾(3.3-3.9)
· winform 绘制太阳,地球,月球 运作规律