Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips














































Working with SQL Server Profiler Trace Files

MSSQLTips author Bru Medishetty By:   |   Read Comments (3)   |   Related Tips: More > Profiler and Trace
Problem

In a previous tip we looked at the steps to Create a Trace Template in Profiler. In this tip we will look at a few more tips such as creating a trace template from an existing trace file and saving a trace file to a SQL Server table.

Solution

Creating a trace template from an existing trace file.

Why and how is this useful?

This is useful when you have a trace file stored in the past, especially where the events that are chosen are not available in a default trace or existing trace templates. Assume that as part of routine system diagnostics, you fired a profiler trace and chose certain events and moreover you applied some column filtering to track only those records you are interested in. You might require such a custom trace on a regular frequency. If you have not already created a trace template, then it can be created based on the old trace file you saved last time.

Steps to do this.

First we need to know the location of the old trace file. Once we are aware of this, we need to open the SQL Server Profiler. Upon opening Profiler, in the menu bar go to Open -> Trace File, as shown in the picture below.

creating a trace template from an existing trace file

The Open File dialog box appears and we need to navigate to the location where the trace file is saved. Choose the trace file and click the Open button on the dialog box.

open sql server profiler

Profiler loads the contents of the trace file as shown in the image below. The trace file in this example is based on the Database events and I selected Data and Log file growth events and filtered to trace only the AdventureWorks database.

in this example the trace file is based on the database events

From the menu bar select Save As-> Trace Template option (see image below).

save as >trace template

Type the new Template name. Choose a template name as appropriate as possible, to reflect the events selected in that trace. This way, the next time you need to use this Profiler trace you just look a the name instead of having to open it and look at the events that it captures.

chhose a name for the template

Once the trace template is saved you can use this trace template for any new trace you run. To do this, in the General tab, choose the trace template from the list of available templates.

trace template type sql server 2008

Saving a profiler trace inside a database table.

Why and how is this useful?

Saving a profiler trace in a database table has multiple benefits. One way is that you can query the table for more granular level details. By adding multiple WHERE clauses to your query you can find only those records you are interested in. One of my personal favorites is retrieving data for a certain time period. This way I can look at those details between 4:00 AM to 5:00 AM. Moreover I can add other conditions to dig deeper and weed out non-important data while trying to solve a particular issue.

One other important benefit is we can delete data from the table that is not relevant making it easier while viewing the trace from Profiler.

Steps to do this.

Open an existing trace file in SQL Server Profiler (described earlier in this tip). In the menu bar go to Save As -> Trace Table, as shown in the picture below.

open an existing trace file in sql server profiler

We need to provide the authentication details to connect the Server in which we want to store the trace table. From the picture below, you can see that I am connecting using SQL Server Authentication.

connect using sql server authentication

Upon successful connection to the Server, we are required to choose the database from the drop down list of the databases, by default the master database is displayed. I selected AdventureWork, but it would be a good idea to setup another database to store this data to keep it segmented from your other data. After that, we have to type in the name of the table if we are creating a new table, alternately you can choose an existing table which you are sure is no longer required. This is because the existing table will be overwritten, causing you to lose the entire table, the structure of the table and all data in it.

its a good idea to set up another database to store this data

Once the trace is saved in a table, we can query it from SQL Server Management Studio (picture below).

Once the trace is saved in a table, we can query it from SQL Server Management Studio
Next Steps
  • Read other tips related to Profiler Trace
  • It would be a good idea to create a separate database to store these trace tables. This way the data is segmented from your user data. Also, if you don't care if you loose the data you don't need to backup the database. You can create a standard set of queries or stored procedures to use against these tables. If you have a bunch of these tables you could use a synonym to point to the table you want to query without having to recode the queries or stored procedures.


Last Update: 1/27/2011


About the author
MSSQLTips author Bru Medishetty
Bru Medishetty focuses on Performance Monitoring and Tuning as well as planning and implementing Disaster Recovery solutions.

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Friday, January 28, 2011 - 1:24:18 PM - Sanjay Read The Tip

Excellent


Thursday, March 28, 2013 - 2:30:20 AM - vijay ahire Read The Tip

I have schedule trace using sql job in sql2008 R2,I want to submit these  .trc generates files to Database tunning advisor,but during submit it asking select database for workload? what its significance?


Thursday, June 13, 2013 - 7:38:22 PM - zlab Read The Tip

This is my procedure to query default trace for event like autogrow (EventClass 92 and 93 for data and log).

Hope can be useful: http://zaboilab.com/sql-server-toolbox/anayze-sql-default-trace-to-investigate-instance-events

 

 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.