DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to query the default trace file?

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

 

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE