How to capture a circular SQL Server Profiler Trace
How to capture a circular SQL Server Profiler Trace
Circular SQL ProfilerTrace:
A circular SQL Profiler Trace is a trace that is re-written once the file (or the total size of all files) has arrived at the size you have chosen as maximum size. When the Profiler Trace arrives at the maximum size you mentioned in the configuration parameters, it starts to re-write from the beginning of the trace.
When should be used:
A circular SQL Profiler trace it is very useful if you have a random issue or error message, which appears on the SQL Server database.
Why should be used:
Because the issue you encounter appears randomly you cannot know the next time when it will appear, so you cannot schedule an action plan or a SQL Profiler Trace to be started when the issue will be reproduced.
A circular SQL Profiler Trace is the best option in this case. The trace writes continuously and, as I said before it is re-written when it arrives at the maximum size you mentioned, so it will not consume all the available disk you have.
This way, with little resources, you can capture what is happening when the error message or the problem occurs, no matter that it appears randomly.
Steps how to capture the circular SQL Profiler Trace:
Step1:
First, you will have to configure an SQL Profiler Trace Template to capture the information and the events you think are useful and can help you identify the issue or to identify the cause. To configure an SQL Profiler Template all you have to do is to follow the steps in the following link: https://technet.microsoft.com/en-us/library/ms175070(v=sql.100).aspx .
To choose the necessary events as part of the Template and to see what it means each of them you can access the following link: https://technet.microsoft.com/en-us/library/ms175481(v=sql.100).aspx
Step2:
After you configured the template, you will have to script the template definition.
So, after you configured the template and saved it, you will have to open a new trace using the template you configured before: File-New Trace. In the Use Template section you will have to choose the name of the template configured and click Run. As the Profiler Trace is running you have to go to New - Export – Script Trace Definition - For SQL Server 2005-SQL11. You have to choose a path where you want to save the script and click Save.
Step3:
Now you have scripted the SQL Profiler Trace and you have to modify the configuration parameters so you can specify: a path where you want to save the SQL Trace Profiler files, the maximum size and, the option to be a circular trace (or rollover).
At the beginning of the scripted trace you have the following configuration parameters.
You have to make sure you have the parameters:
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @options int
declare @filecount int
set @maxfilesize = 300
exec @rc = sp_trace_create @TraceID output, 2, N' InsertFileNameHere, @maxfilesize, NULL, 5
if (@rc != 0) goto error
Please replace the text InsertFileNameHere, with an appropriate filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension will be appended to the filename automatically. If you are writing from remote server to local drive, please use UNC path and make sure server has write access to your network share.
The parameters above are explained in the link: https://msdn.microsoft.com/en-US/library/ms190362(v=sql.100).aspx
In the example above the @maxfilesize parameters is 300 M. It means that the maximum size for each trace file is 300M. The @options parameter is 2 which means the trace rollover is enabled and the @filecount is 5, meaning the maximum number of rollover files is 5.
You can change the value for each parameter to meet your requirements.
After all changes are done you have to save the file. To import this new template in the SQL Server Instance you will have to execute via SQL Server Management Server the script you modified.
By default the status of the trace is enabled and the trace is running, you can see that at the end of the script in the command:
exec sp_trace_setstatus @TraceID, 1
To only configure the template without starting the trace you will have to execute the script but before that, you have to change the parameter to 0:
exec sp_trace_setstatus @TraceID, 0
Step4:
You can use the following link to see how you can change the status for the trace: https://msdn.microsoft.com/en-us/library/ms176034(v=sql.100).aspx
To see the trace you have just created you can use the following command:
select * from ::fn_trace_getinfo(DEFAULT)
As example you can use the following scripts to stop, start or delete the trace from SQL Server Management Studio.
Start the trace:
Begin
declare @TraceId integer
select @TraceId=traceid from ::fn_trace_getinfo(DEFAULT)
where property=2
exec sp_trace_setstatus @TraceId,1
END
GO
Stop the trace:
Begin
declare @TraceId integer
select @TraceId=traceid from ::fn_trace_getinfo(DEFAULT)
where property=2
exec sp_trace_setstatus @TraceId,0
END
GO
Delete the trace:
Begin
declare @TraceId integer
select @TraceId=traceid from ::fn_trace_getinfo(DEFAULT)
where property=2
exec sp_trace_setstatus @TraceId,2
END
GO
After the random issue is reproduced you have to stop the trace and start the analysis of the results.
If you think the template that you created is no longer useful you can delete is using the examples above.
I hope the information that I provided above was helpful and opened new ways of troubleshooting random issues or at least provided more information how to collect data when the problem occurs.