當(dāng)實(shí)例沒(méi)有做DDL Trigger和其它一些監(jiān)控時(shí)，如何知道誰(shuí)刪除了某個(gè)表？通過(guò)系統(tǒng)函數(shù)fn_dblog,fn_dump_dblog和默認(rèn)跟蹤可以找到。

1. 創(chuàng)建測(cè)試環(huán)境：新建個(gè)表，插入一條數(shù)據(jù)，然后drop掉

CREATE DATABASE test
go
USE test
go
CREATE TABLE dbo.fnlog_test
(id INT IDENTITY ,val VARCHAR(10) x')
GO
CREATE CLUSTERED INDEX IX_ft_id
ON dbo.fnlog_test (ID)

GO
INSERT INTO dbo.fnlog_test
VALUES (DEFAULT )
GO
DROP TABLE fnlog_test
GO

2. 通過(guò)sys.fn_dblog,找出相關(guān)信息：

USE test
go
SELECT [Transaction ID],[Transaction Name],[Begin Time],[Server UID],SPID
FROM sys.fn_dblog(NULL,null)
WHERE [Transaction Name]='DROPOBJ'
go

3. 上一步中這里得到了事務(wù)ID，開(kāi)始時(shí)間，Suid,SPID等，但是執(zhí)行刪除的SPID可以已經(jīng)logout或者被重用了。所以要找出“當(dāng)時(shí)”的這個(gè)SPID。

先根據(jù)事務(wù)ID，找出被刪除的對(duì)象吧。查詢結(jié)果的“OBJECT: 9:245575913:0”，9是DB_ID,245575913是object_id,就是被刪除的表的object_id.

SELECT TOP(1) [Lock Information]
FROM sys.fn_dblog(NULL,NULL)
WHERE [Lock Information] %SCH_M OBJECT%' AND [Transaction ID]='0000:000002e7'
go

4. 通常SQL Server實(shí)例安裝后會(huì)開(kāi)啟一個(gè)默認(rèn)跟蹤(Default Trace)，這個(gè)跟蹤會(huì)記錄一引起級(jí)別較高的重要信息。先找到默認(rèn)跟蹤

SELECT id,status,path FROM sys.traces
WHERE is_default=1

5. 根據(jù)前幾步中得到的trace path,事務(wù)ID，開(kāi)始時(shí)間，SPID，object_id，通過(guò)默認(rèn)跟蹤得到進(jìn)一步的信息：

SELECT DatabaseID,NTUserName,HostName,ApplicationName,LoginName,
SPID,ObjectID,StartTime, EventClass,EventSubClass
FROM sys.D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\log_10.trc',1)
WHERE SPID=52 AND  StartTime>'2013/07/15 11:32:44:133' AND ObjectID =245575913
GO

這一步中就得到了誰(shuí)刪除了這個(gè)表的更具體信息了。需要說(shuō)明一下的是EventClass=47,EventSubclass=(0,1)，這記錄了跟蹤事件的操作。

SELECT te.trace_event_id,te.name,tsv.subclass_value,tsv.subclass_name
FROM sys.trace_events te
INNER JOIN sys.trace_subclass_values tsv
ON te.trace_event_id=tsv.trace_event_id 
WHERE te.trace_event_id=47 AND tsv.subclass_value IN(0,1)

6. 如果是生產(chǎn)環(huán)境的，事務(wù)日志可能被截?cái)喽恢赜酶采w了。這里就需要從日志備份中讀取日志信息來(lái)定位。需要用到fn_dump_dblog.

  重新構(gòu)建測(cè)試環(huán)境：

CREATE DATABASE test
go
USE test
go
CREATE TABLE dbo.fnlog_test
(id INT IDENTITY ,val VARCHAR(10) x')
GO
CREATE CLUSTERED INDEX IX_ft_id
ON dbo.fnlog_test (ID)

GO
INSERT INTO dbo.fnlog_test
VALUES (DEFAULT )
GO
USE master
go
BACKUP DATABASE test
TO D:\SQLSample\test.bak'
WITH init
go
USE test
go
DROP TABLE fnlog_test
GO
USE master
go
BACKUP LOG test
TO D:\SQLSample\test.bck'
WITH init
go
2. 和3. 的查詢要換成fn_dump_dblog，其它的步驟是一樣的。這里我另外做的測(cè)試，所以事務(wù)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] %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] %SCH_M OBJECT%' AND [Transaction ID]='0000:000002b8'

總結(jié)：

  1. 在SQL Server 2008 R2 SP2&SQL Server 2012 SP1測(cè)試通過(guò)

  2. trace文件是rollover的，所以要找對(duì)path，同樣要從日志備份中查詢的話，也要找對(duì)日志備份文件的時(shí)間

  3. fn_dblog和fn_dump_dblog是Undocumented Function.