First of all let me explain you why you need to know the user defined profiler template location.
In situations you may need to define a template and share it with other DBAs to run or vice versa. You have saved the profiler and request the other person to run by providing the name of the profiler. The other person called you (when you are not in shift) since he/she is not able to find the profiler you created, but that is hard for you to believe as you are quite sure you did it right.
Once you go through this article you will get to know:
- How to create a user defined template?
- Why others are not able to see the template?
- How to resolve this?
You can watch the following video demo which shows the stuffs I am going to explain here:
1. How to create a user defined template?
Launch SQL Server Management Studio (SSMS) and open SQL Server profiler:
Open a new Template:
Provide a template name and choose a base template from the drop down menu. In the below example I have chosen Standard (default) template. Then click on Events Selection tab.
In the above example I have not selected the check box for “Use as a default template for selected server type”. You can choose this option if you want your template to be default template.
Choose the Events as per your requirement. Click on “Show all events” and “Show all columns” to view all the events and columns to choose from. Then click on “Column Filters” to put filter for the trace.
In the below example I have put a filter for database id since I wanted to capture the transactions only for one database. The filter may differ for your requirement.
Once you are done with the filter selection click on OK and then Save the template.
Now if you go back and check the drop down list of templates you will find your custom template.
Go to File > New Trace and then connect to any SQL Instance
The following screen shot shows the newly created template “Demo Template (user)” in the drop down list.
2. Why others can’t see this template under the same drop down?
Here is the physical location of the user defined template file:
C:\Users\UserName\AppData\Roaming\Microsoft\SQL Profiler\11.0\Templates\Microsoft SQL Server\110
The main point to note here is the template gets created under the user profile folder of the login id, used to login to the server and that is why it is not visible to others.
AppData is a hidden folder and 11.0 and 110 will change depending upon the SQL Server version.
3. How to resolve this?
Let’s see the location of SQL Server Provided profiler templates.
C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Profiler\Templates\Microsoft SQL Server\110
You guessed it right! You just need to copy the user defined template from
C:\Users\UserName\AppData\Roaming\Microsoft\SQL Profiler\11.0\Templates\Microsoft SQL Server\110
To
C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Profiler\Templates\Microsoft SQL Server\110
Hope you enjoyed this article and feel free to share if you think it is helpful. Please let me know if you have any questions on this.
You might also want to check Myth around Remote Query Timeout Option