Sql Server by default will run a default trace and these trace files are located in log directory. The contents of the trace file can be read using
Select * from sys.fn_trace_gettable('TraceFilePath',default)
Example :
Select * from sys.fn_trace_gettable
('E:\MSSQL10_50.SQL2008R2\MSSQL\Log\log_160.trc',default)
You can also use this query to read the information from the profiler or server side trace. Also, Microsoft does not recommend using function sys.fn_trace_gettable or Trace method to capture the server activity as this feature is going to be removed in future. Use Extended Events instead.
If you do not find the information in default trace to be useful, you can disable the trace by sp_configure. Firstly,enable advanced options using sp_configure.
sp_configure ‘show advanced options’,1
GO
Reconfigure
GO
sp_configure ‘default trace’ , 0
GO
Reconfigure
GO
sp_configure ‘show advanced options’,0
GO
Reconfigure
This turns off the small trace files the sql server writes by default.
The max size of each file can be 20480 KB. This default trace can be useful sometimes to know the events happened on the server during the last moments.
SQL Server Repository 