使用事务日志文件来找到被drop的表
使用事务日志文件来找到被drop的表
http://www.sqlskills.com/blogs/paul/finding-out-who-dropped-a-table-using-the-transaction-log/
I came across a question on ServerFault this afternoon that inflamed my desire to be ultra-geeky (it was really already inflamed after teaching backup and restore internals all afternoon). Basically the question boiled down to how to find out who dropped a table if there’s no other way except the transaction log (e.g. no tracing is available, even the default trace). So I hacked around and figured out at least how to find out *when* a particular table was dropped plus who dropped it.
Everything hinges on using undocumented commands to look into the transaction log. I’ve played with this before on the blog: fn_dblog.
First off I created a script to create a database, populate a table and then drop it.
USE [master];
GO
CREATE DATABASE [FnDbLogTest];
GO
USE [FnDbLogTest];
GOCREATE TABLE [TestTable] (
[c1] INT IDENTITY,
[c2] CHAR (100) DEFAULT ‘a’);
GO
CREATE CLUSTERED INDEX [TT_CL] ON [TestTable] ([c1]);
GO
INSERT INTO [TestTable] DEFAULT VALUES;
GOSELECT OBJECT_ID (N’TestTable’);
GODROP TABLE [TestTable];
GO
First we need to find the transactions that drop tables in the log:
SELECT [Transaction Id], [Begin Time], SUSER_SNAME ([Transaction SID]) AS [User]
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = N’DROPOBJ’;
GO
The (NULL, NULL) is the starting LSN and ending LSN to process – NULL means process everything available.
Results are:
Transaction Id Begin Time User
————– ———————– —————
0000:000000e0 2009/06/16 18:23:03:320 APPLECROSS\Paul
Now, this only shows us that a table was dropped, not which table it was. There’s no way to get the name of the table that was dropped, only the object ID – so you’ll need to have some other way to determine what the table ID is if there are multiple table drops and only one of them is malignant.
This code will give you the object ID of the dropped table:
SELECT TOP (1) [Lock Information] FROM fn_dblog (NULL, NULL)
WHERE [Transaction Id] = ’0000:000000e0′
AND [Lock Information] LIKE ‘%SCH_M OBJECT%’;
GOLock Information
————————————————
HoBt 0:ACQUIRE_LOCK_SCH_M OBJECT: 25:245575913:0
The 25:245575913 is the database ID and object ID of the table that was dropped, and you can look up the object ID in an earlier backup of the database.
Now you can go find whoever it was and take whatever action you deem appropriate :-)
Hope this helps!
PS If you find the you don’t get enough info from fn_dblog, try turning on trace flag 2537. It allows the function to look at *all* possible log, not just the active log.
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战