当实例没有做DDL Trigger和其它一些监控时，如何知道谁删除了某个表？通过系统函数fn_dblog,fn_dump_dblog和默认跟踪可以找到。

1. 创建测试环境：新建个表，插入一条数据，然后drop掉

test

test
dbo.fnlog_test
(id ,val (10) 'x')
IX_ft_id
dbo.fnlog_test (ID)

dbo.fnlog_test
( )
fnlog_test

2. 通过sys.fn_dblog,找出相关信息：

test

[ ID],[ Name],[ ],[Server UID],SPID
sys.(,)
[ Name]='DROPOBJ'

 

3. 上一步中这里得到了事务ID，开始时间，Suid,SPID等，但是执行删除的SPID可以已经logout或者被重用了。所以要找出“当时”的这个SPID。

先根据事务ID，找出被删除的对象吧。查询结果的“OBJECT: 9:245575913:0”，9是DB_ID,245575913是object_id,就是被删除的表的object_id.

(1) [Lock Information]

sys.(,)
[Lock Information] '%SCH_M OBJECT%' [ ID]='0000:000002e7'

4. 通常SQL Server实例安装后会开启一个默认跟踪(Default Trace)，这个跟踪会记录一引起级别较高的重要信息。先找到默认跟踪

id,status,path sys.traces

is_default=1

5. 根据前几步中得到的trace path,事务ID，开始时间，SPID，object_id，通过默认跟踪得到进一步的信息：

DatabaseID,NTUserName,HostName,ApplicationName,LoginName,

SPID,ObjectID,StartTime, EventClass,EventSubClass
sys.('D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\log_10.trc',1)
SPID=52   StartTime>'2013/07/15 11:32:44:133' ObjectID =245575913

这一步中就得到了谁删除了这个表的更具体信息了。需要说明一下的是EventClass=47,EventSubclass=(0,1)，这记录了跟踪事件的操作。

SELECT te.trace_event_id,te.name,tsv.subclass_value,tsv.subclass_nameFROM sys.trace_events teINNER JOIN sys.trace_subclass_values tsvON te.trace_event_id=tsv.trace_event_id WHERE te.trace_event_id=47 AND tsv.subclass_value IN(0,1)

6. 如果是生产环境的，事务日志可能被截断而被重用覆盖了。这里就需要从日志备份中读取日志信息来定位。需要用到fn_dump_dblog.

  重新构建测试环境：

test

test
dbo.fnlog_test
(id ,val (10) 'x')
IX_ft_id
dbo.fnlog_test (ID)

dbo.fnlog_test
( )
master
test
='D:\SQLSample\test.bak'
init
test
fnlog_test
master
LOG test
='D:\SQLSample\test.bck'
init
2. 和3. 的查询要换成fn_dump_dblog，其它的步骤是一样的。这里我另外做的测试，所以事务ID与前面不同了。

SELECT [Transaction ID],[Transaction Name],[Begin Time],[Server UID],SPID FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'D:\SQLSample\test.bck',  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) WHERE [Transaction Name] LIKE '%DROPOBJ%'SELECT TOP(1) [Lock Information]FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'D:\SQLSample\test.bck',  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,  DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) WHERE [Lock Information] LIKE '%SCH_M OBJECT%' AND [Transaction ID]='0000:000002b8'

总结：

  1. 在SQL Server 2008 R2 SP2&SQL Server 2012 SP1测试通过

  2. trace文件是rollover的，所以要找对path，同样要从日志备份中查询的话，也要找对日志备份文件的时间

  3. fn_dblog和fn_dump_dblog是Undocumented Function.

-------------------------------------

作者：