Hi, I'm trying to run a trace with Sql Server Profiler against an Asp.NET Website Application running in Visual Studio development server.
However, whenever the trace is running, all db requests from the web application fails giving me the error message:
"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
If I stop the trace, the web application works again.
Any input on this is appreciated.
Once you get around the profiler timeout issue you should look at tuning your database (if you havent already, although it doesnt sound like it).
I have had a similar issue recently and it turned out to be IO blocking due to high reads on certain querys/statements. Getting the profiler to run on top of an already sluggish database was difficult. We had to run the profiler in ten minute sections at quieter times, although this does not help to identify the biggest issues with the heaviest loads.
Once we got the profiler to capture data (on sql server 2005) and implemented the indexes and statistics recommeneded by the Database Tuning Advisor (DTA) the database was running at expected peformance levels again.
I would recommend you read this free ebook on sql server profiler....
http://www.red-gate.com/products/SQL_Response/offers/mastering_sql_profiler_ebook.htm
It details how to run lightweight traces that will help the DTA recommend indexes and statistics that will improve the performance of your database and also identify some slow running queries that could be located in your code.
The trace you are running could be tipping your database over the edge, so running it in 10-20 minute sections might be more feasable.
If you have IO blocking issues this affects the overall sql server in general and management studio will seem non responsive at times.
You just need to increase the CommandTimeout on the sql connection while you are debugging and the application pool timeout values as well.
Is it possible that you're accidentally stuck in single-user mode?
Try this:
ALTER DATABASE [database name] SET MULTI_USER;