3

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dan Pettersson
  • 713
  • 5
  • 17
  • Does profiler capture events successfully whilst this is happening? What version of SQL Server are you on? – Martin Smith Jul 03 '10 at 08:31
  • No, nothing is captured. I'm on SQL Server 2008. – Dan Pettersson Jul 03 '10 at 09:15
  • I've never come across this issue myself. Does sound strange. do you have any better luck tracing with extended events rather than Profiler? (If you've not used this before this answer here might help you get started http://stackoverflow.com/questions/3094735/create-trigger-to-log-sql-that-affected-table/3149717#3149717) – Martin Smith Jul 04 '10 at 00:29
  • @Martin Hm... Before I go that path, do you think it could have anything to do with maximum concurrent connections allowed to the db? (Thinking it accidentally has been set to 1 on my dev machine). I tried to google that but couldn't find a straight answer on how to check or change that value/setting... – Dan Pettersson Jul 04 '10 at 16:09
  • To check the setting in management studio right click the root node for that SQL Server instance, Properties on the menu then Connections tab on the popup dialogue. – Martin Smith Jul 05 '10 at 22:23

3 Answers3

1

You just need to increase the CommandTimeout on the sql connection while you are debugging and the application pool timeout values as well.

Bron Davies
  • 5,930
  • 3
  • 30
  • 41
  • Yes, changing the CommandTimeout seems to do the trick. Since I'm using the asp.net membership- and roleprovider, I hade to set the commandTimeout property on those elements in the web.config file as well. – Dan Pettersson Jul 23 '10 at 15:51
0

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.

Baldy
  • 3,621
  • 4
  • 38
  • 60
  • Seems I have to check out that book to really get how I'm supposed to do the profiling in production, since doing the DTA stuff was the reason for profiling in the first place. Thanks for your input. – Dan Pettersson Jul 23 '10 at 15:52
0

Is it possible that you're accidentally stuck in single-user mode?

Try this:

ALTER DATABASE [database name] SET MULTI_USER;

Jason
  • 951
  • 5
  • 11