|
The default trace is always running in the background of an SQL Server instance - when enabled - capturing events that may help the DBA in troubleshooting. The SQL Server default trace is enabled by default. It is a lightweight trace, therefore it does not slow down the system. The following
Microsoft SQL Server T-SQL code sample scripts demonstrate the usage of the Default Trace file for object altered information.
-- Check if default trace is enabled
EXEC sp_configure 'default trace enabled'
-- Enable default trace
EXEC sp_configure 'default trace enabled', 1
RECONFIGURE
------------
-- Trace file information
SELECT * FROM sys.traces
------------
-- Current default trace rollover file
DECLARE @Path varchar(256) = (
SELECT CONVERT(varchar(256),VALUE )
FROM ::fn_trace_getinfo(0)
WHERE property = 2 ); SELECT @Path
-- C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Log\log_122.trc
-- Query the default trace rollover file for altered objects
SELECT loginname AS LoginName,
loginsid,
spid,
hostname AS HostName,
applicationname,
servername AS ServerName,
databasename AS DatabaseName,
objectName,
e.category_id,
cat.name AS CategoryName,
textdata,
starttime,
e.name AS EventName
FROM ::fn_trace_gettable(@Path, 0)
INNER JOIN sys.trace_events e
ON eventclass = trace_event_id
INNER JOIN sys.trace_categories AS cat
ON e.category_id = cat.category_id
WHERE databasename = 'AdventureWorks2008'
AND objectname IS NULL
AND e.name ='Object:Altered'
/* LoginName loginsid spid HostName applicationname ServerName DatabaseName
objectName category_id CategoryName textdata starttime EventName
HPESTAR\SQLAdmin 0x01050000000000051500000083A66141640B5ED2C6FD4509E9030000 54
HPESTAR Microsoft SQL Server Management Studio HPESTAR\SQL2008 AdventureWorks2008
NULL 5 Objects NULL 2011-03-27 05:49:05.043 Object:Altered
*/
Related articles:
Using the Default Trace in SQL Server 2005 and SQL Server 2008
default trace enabled Option
|
