MSSQL2008下查看结构变化监控，Schema Change History

最新推荐文章于 2024-08-26 03:17:13 发布

starseeker7

最新推荐文章于 2024-08-26 03:17:13 发布

阅读量599

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL技术

本文链接：https://blog.youkuaiyun.com/starseeker7/article/details/8555928

SQL技术专栏收录该内容

37 篇文章

订阅专栏

本文介绍了在MSSQL2008中使用Schema Change History来跟踪数据库对象的改动，包括其局限性和解决办法。DBA可以通过SSMS的标准报告查看历史，但该功能依赖于default trace enabled选项，并存在延迟问题，且信息有限。为克服这些限制，提出了自定义报表的解决方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

DBA经常会被问到：我们的对象(表，视图，函数，存储过程等)被意外删除(修改)，我想知道是谁干的？什么时候做的？从那个HostName连接过来的？是ApplicationName是什么？如何查看？

1. SSMS自带的SchemaChange History功能

a) Right click on database, forwhich you want to view the report

b) Go to Reports->StandardReports->schema Change history

实际上，SQL Server有一个高级配置选项（默认是开启的），名称叫：defaulttrace enabled。它作用是SQL Server开启一个默认的Trace Profiler，Trace的文件放到目录Dump Directory，在此目录存在5个归档文件名为Log_XXXX.trc，如下图所示：

SSMS中的Schema Change History就是读取这个Trace文件获取到的结果集。

局限性

a) 基于上面原理的分析，如果我们这个高级选项关闭，则无法获取到这个报表

b) 根据我的测试，它的及时性得不到体现（猜测也许是SSMS有获取Trace文件的时间间隔，在没有达到时间间隔阀值的时候，是读取缓存的）

c) 获取到的信息量非常的少，顶多只能得到是那个Login在什么事件做了什么DDL操作，无法获取到一些非常重要的信息，比如：hostname, Applicationname等

2 根据Schema Change History原理，自定义报表

基于方案1的分析，至少存在3个典型的局限性，为了避免这些缺点，我们有了方案2。方法如下：

USE master
GO

;WITH DATA
AS
(
    SELECT 
        TRA.ObjectName
        ,TRA.HOSTNAME
        ,TRA.ApplicationName
        ,TRA.LoginName
        ,TRA.StartTime
        ,TRA.EventClass
        ,TRA.DatabaseName
        ,TRA.EndTime
        ,TRA.ServerName
    FROM  sys.traces AS tr
        cross apply fn_trace_gettable(left(path ,LEN(path) - CHARINDEX('\', REVERSE(path))) + N'\log.trc',default) AS TRA
    WHERE tr.is_default = 1
    AND TRA.EventClass in(46,47,164) 
    AND TRA.EventSubclass = 0 
    AND TRA.DatabaseID = DB_ID('DBName')
)
SELECT 
    TRA.HostName
    ,TRA.ServerName
    ,TRA.LoginName
    ,TRA.ApplicationName
    ,TRA.DatabaseName
    ,TRA.ObjectName
    ,EVT.name AS Action
    ,TRA.StartTime
    ,TRA.EndTime
FROM DATA AS TRA
INNER JOIN sys.trace_events AS EVT
   ON TRA.EventClass = EVT.Trace_Event_ID
ORDER BY TRA.StartTime

解决了b)和c)两个局限性，但是还是受限于default trace enabled这个高级配置选项。