How to enable default trace - Troubleshoot Trace property

Long back I was facing an interview where I was been asked whither we should leave the default trace on or not. My answer was YES we should leave it on. - Why? Microsoft recommended that it should be off. The interviewer replied. - Microsoft didn't do such recommendation any where, I replied. In fact they encourage you to leave it on because thus how you can view a lot of information about your system. One of the big one is Schema change. As a DBA you will always be Challenged in the lower environment or even in the higher environment someone might messed up your schema and if that is not on and if you have no third party tools to monitor that you have no place to go. Therefore, I always like to have it on. In my recent job I have noticed one of my server has default trace ON. However, when I query select * from :: fn_trace_getinfo(default) It returned nothing. Ideally it should return something like this _________________________________________________________________________________________ Traceid property value 1 1 2 1 2 D:\mssql\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_1686.trc 1 3 20 1 4 NULL 1 5 1 ______________________________________________________________________________________ When I run this DECLARE @dir NVARCHAR(1000) EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLDataRoot', @dir output, 'no_output' SELECT @dir + N'\Log\log.trc'; I got D:\mssql\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log.trc But select * from :: fn_trace_getinfo(default) has not info. After Googling every where I have founf no solution and I thought let me disable and reenable -- Disabled the default trace EXEC sp_configure 'default trace enabled', 0 GO RECONFIGURE with override -- Enabled the default trace EXEC sp_configure 'default trace enabled', 1 GO RECONFIGURE with override Now let's verify EXEC sp_configure 'default trace enabled' GO All looks good Do the query select * from :: fn_trace_getinfo(default) ____________________________________________________________________ traceid property value 1 1 2 1 2 D:\mssql\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_1686.trc 1 3 20 1 4 NULL 1 5 1 _____________________________________________________________________________ So the trick is disable and enable the default trace _____________________________________________________________________________________