用SQL Server默认跟踪查看近期数据库对象的DDL改动

执行如下脚本即可：

DECLARE @filePath NVARCHAR(MAX)
SELECT @filePath=path FROM sys.traces AS t WHERE id=1
SELECT TOP 100
       gt.[HostName],
       gt.[ServerName],
       gt.[DatabaseName],
       gt.[SPID],
       gt.[ObjectName],
       gt.[objecttype] [ObjectTypeID],
       sv.[subclass_name] [ObjectType],
       e.[category_id] [CategoryID],
       c.[Name] [Category],
       gt.[EventClass] [EventID],
       e.[Name] [EventName],
       gt.[LoginName],
       gt.[ApplicationName],
       gt.[StartTime],
       gt.[TextData]
FROM   fn_trace_gettable(@filePath,DEFAULT) gt
       LEFT JOIN sys.trace_subclass_values sv
            ON  gt.[eventclass] = sv.[trace_event_id]
            AND sv.[subclass_value] = gt.[objecttype]
       INNER JOIN sys.trace_events e
            ON  gt.[eventclass] = e.[trace_event_id]
       INNER JOIN sys.trace_categories c
            ON  e.[category_id] = c.[category_id]
WHERE  gt.[spid] > 50		   --50以内的spid为系统使用
	   --AND gt.[DatabaseName] = 'master' --根据DatabaseName过滤
	   --AND gt.[ObjectName] = 'fn_trace_getinfo' --根据objectname过滤
       AND e.[category_id] = 5 --category 5表示对象，8表示安全
       --AND e.[trace_event_id] = 47
                               --trace_event_id
                               --46表示Create对象（Object:Created），
                               --47表示Drop对象（Object:Deleted），
                               --93表示日志文件自动增长（Log File Auto Grow），
                               --164表示Alter对象（Object:Altered），
                               --20表示错误日志（Audit Login Failed）
ORDER BY
       [StartTime] DESC

SQL Server默认跟踪：

SQL Server有Default Trace默认跟踪，默认情况是开启的。数据库记录信息到log.trc文件，可以查看trace_event_id，46表示Create对象（Object:Created），47表示Drop对象（Object:Deleted），93表示日志文件自动增长（Log File Auto Grow），164表示Alter对象（Object:Altered），20表示错误日志（Audit Login Failed）。


缺点：5个文件滚动更新（每个文件20MB），太久的数据找不到;


能查看的内容：
1)  使用Default Trace查看谁还原了你的数据库
2)  数据库中那些对象被created /altered /deleted
3)  查找日志文件快速增长的原因
4)  查看、过滤Login failed for user 'sa'等错误信息

相关脚本：

--查询Default Trace是否开启: value=1 即为开启
SELECT * FROM sys.configurations WHERE configuration_id = 1568;

--开启Default Trace
sp_configure 'show advanced options' , 1 ;
GO
RECONFIGURE;
GO
sp_configure 'default trace enabled' , 1 ;
GO
RECONFIGURE;
GO
--测试是否开启
EXEC sp_configure 'default trace enabled';
GO
--关闭Default Trace
sp_configure 'default trace enabled' , 0 ;
GO
RECONFIGURE;
GO
sp_configure 'show advanced options' , 0 ;
GO
RECONFIGURE;
GO

--获取当前跟踪文件的路径
/*
1：trace options，有2、4、8 三个值，请参考sp_trace_create；
--2. 表示当文件写满的时候，关闭当前跟踪并创建新文件。
--4. 表示如果不能将跟踪写入文件，不管什么原因导致，SQL Server则会关闭。这个可以利用此选项，追踪问题
--8. 制定服务器产生的最后5MB的跟踪信息记录由服务器保存。
2：file name，更准确来说是trace文件的路径；
3：max file size，设置最大滚动文件大小，当达到这个值就会创建新的滚动文件；
4：stop time，设置trace停止的时间；
5：当前状态(0=stopped, 1=running) ；
*/
SELECT * FROM ::fn_trace_getinfo(0)

SELECT * FROM sys.traces AS t

参考文档：

点击打开链接