Yesterday evening the DBA Team wanted to run a trace against a Production as requested by the Application Team. Since this server is in a strictly controlled environment, a Change Control had to be prepared. The Implementer of the Change Control would not be the one documenting it. Hence the documentation has to be clear and should meet the requester’s expectations. So one of the DBA started off with the documentation process for the Change Control. Half way through it, the process seemed to cumbersome. Why? He had to write “Click Event Selection, Click TSQL, Select Application Name” etc.
Since the implementer of the Change Control will be different, it was decided that the Implementer will have to execute the trace based on a custom template. The new template was created as below.
Opened SQL Server Profiler, chose Templates –> New Template.
Since the server that we had to run the trace against was a SQL Server 2005 instance, the Server Type was selected as SQL Server 2005 in the Trace Template properties.
Then selected the requisite events
After all this selection, clicked on Save to save the trace template. The first step was to open a new trace to see if the template is listed there or not.
Cool. The new template was getting listed. However, to confirm the DBA asked his buddy to logon to the server and see if the New Template is getting listed for him as well. No, he could not see it. The DBA who did the documentation checked the screen rubbing his eyes (already late in the evening) and he could see it.
Now the question was where did the trace template disappear for the other user? It was clear that the trace template was saved locally on the user profile of the one who created it. By default, SQL Server Profiler saves the Trace Templates under C:\Documents and Settings\Administrator\Application Data\Microsoft\SQL Profiler\[VersionNumber]\Templates\Microsoft SQL Server\[Version# SelectedWhileCreatingTemplate] directory. In this case it was saved in
Now the location of Trace Template has been found, all he had to do was to move it to a common location, so that anyone can double click on it and Import. In order to have this template available to all users without having to manually import it, a copy of the Trace Template needs to be copied to the below location.
[SQL Server installation directory]\[VersionNumber]\Tools\Profiler\Templates\Microsoft SQL Server\[VersionNumber]\
In our case, this directory was
Despite the last minute rush, the documentation was prepared and the Change Control was ready to be implemented later in the day