• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 407
  • Last Modified:

SQL Server 2005 Profiler save multiple traces to the same table

Hi All,

My customer has a question and I don't have an environment to test this. But he is currently saving SQL traces to a SQL table. The problem is when the trace ends (for whatever reason, he says when the target server restarts the trace ends), then saving to that table ends. Which means that the next time he starts a trace and he wants to use the same table name, then it asks him if he wants to overwrite the table or cancel. If he clicks overwrite, then he loses all the trace data he collected before, if he clicks cancel, then he is unable to continue with the trace.

How can he resume saving trace information to the same table?

2 Solutions
There is no way to resume a trace by appending to an old trace output.  You would need to create a separate output and combine them in post-processing.
First of all it is not a good practise to store profiler trace data to a database table. By doing this your loading SQL Server with extra workload. It is always recomended to use a OS file to store trace data.

For your orginally question, you'll have to give new name for the table each time otherwise it will be truncated. Having different files you can use UNION to join them and get the result in to a single result set.
DangerizAuthor Commented:
I guess the customer will have to resort to post-processing the results.

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now