This is one of the hidden features of SQL Server, by enabling this feature, sql server records all the traces running behind the scenes and in case, the server crashes, will record it into a file. Enabling this feature, is not a big task, but you have to perform these steps manually.
As we all knows, the first step is to Create a trace, that we can do this with 'sp_Trace_Create' stored procedure.
sp_trace_create [ @traceid = ] trace_id OUTPUT
, [ @options = ] option_value
, [ @tracefile = ] 'trace_file'
[ , [ @maxfilesize = ] max_file_size ]
[ , [ @stoptime = ] 'stop_time' ]
DECLARE @TraceId INT;
EXEC sp_trace_create @TraceId OUTPUT, @options = 8;
SELECT @TraceId AS TraceID
The output is the trace which we created, in my case it is '1'
By default the status will be zero, so we need to manually enable this. In order to get the current status, you can run the system function fn_Trace_GetInfo()
SELECT * FROM ::fn_trace_getInfo(1)
the above will show you one similar output
Now, we need to find what this 'Property' and 'Value' fields are
The property of the trace as represented by the following integers:
1 - Trace Options, in this case we passes '8' as '@Options' , so the value for this will be '8'
2 - FileName, i.e. the exact path of the trace file
3 - MaxSize , Maximum size of the above trace file (in MBs, in this case it is 5MB)
4 - StopTime
5 - Current Trace status, a status value of zero means, the trace has not been started yet.
Since our trace is still in not started, we need to manually invoke it, the system procedure 'sp_trace_setStatus' will do this for us.
EXEC sp_Trace_SetStatus @TraceId = 2 , @Status = 1
Now In order to get the values from the trace file use
FROM ::fn_trace_gettable (
'C:\Program Files\Microsoft SQL Server\MSSQL\Data\blackbox.trc', DEFAULT)