SQL Tracing not working

Question

Hi

Both in in Visual Studio 2008 and Visual Studio 2010 beta 2, I have a problem with the SQL Tracing feature when loadtesting.

In "Run Settings" -> "SQL Tracing Connect String", the help text says: "Opens the Connection Properties dialog box. Choose a server in which to store the SQL Tracing data."

This is where I'm confused. Is it the server I want to monitor (as stated here http://msdn.microsoft.com/en-us/library/ms182589.aspx in step 4) or is it the server to store the tracing data?

If I choose the server I want to monitor, then I get a file in the "SQL Tracing Directory" containing all the correct tracing data, but the load test result gives me the following error:

"Exception    SqlException    1    File '\\lhc_loadtest4\SqlTraces\LoadTestTrace.20091113.114125.trc' either does not exist or is not a recognizable trace file. Or there was an error opening the file.".

I can open the file in SQL Server Profiler without any problems.

If I choose the server to store the tracing data, then I get no errors and I can load the SQL tracing data in "Tables" -> "SQL Trace", but this data only contains the tracing data for the test controller results store, and not any tracing data for the server I want to monitor.

How do I enable SQL Tracing in a loadtest for a SQL Server?

Best Regards

Lars

Answer 1

Hello Lars,

The value you set in the 'SQL Tracing Connect String' property should indicate the server you want to monitor but not the server to store the tracing data.

If SQL tracing is enabled, a file is created during the load test run that contains the trace data. This data is automatically saved in the Load Test Results Store at the end of the test run and the trace file is then deleted. You analyze the trace data in the SQL Trace table after your load test has completed.

I did some tests on my machine(VSTS2008 SP1+ SQL SERVER2008) and they worked fine, the file is automatelly deleted at the end of test. So I guess the file is currently in use. What is the edition of your SQL SERVER? You may utilize the tool mentioned in this post to find what process locked it.

In addition, here are some articles about sql tracing will be helpful for your learning:
http://msdn.microsoft.com/en-us/library/ms243169.aspx
http://msdn.microsoft.com/en-us/library/ms182589.aspx
http://msdn.microsoft.com/en-us/library/ms404658.aspx

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Send us any feedback you have about the help from MSFT at [e-mail].

Answer 2

Hi Edwer

Thank you for your reply.

The problem was that my SQL Tracing Directory was set to a share on the load test machine and not the SQL Server to be monitored.

When I choose a share on the load test machine, the trace file got created but would not be deleted after the test is complete (and would not be loaded in the database results store). There where no locks on the file, so it's probably a bug in Visual Studio.

I'm glad I finally got it to work, thanks again for your reply.

Best Regards

Lars

Answer 3

Hello Lars,

It is so great to know you have it resolved. I guess you reset the SQL Tracing Direcotry to the machine where SQL Server is to have it resolved, right? Thank you for sharing your solution.

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Send us any feedback you have about the help from MSFT at [e-mail].