执行如下脚本即可:
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
参考文档: