0

This is not a duplicate question. Differences: No solution on other question works. This is Azure SQL not SQL Server.

I have a stored procedure that runs fine in SQL Server Management Studio and it ran fine ten minutes ago from my C# page, but it's failing now with a time-out. Nothing on the C# page has been changed.

The stored procedure is a SELECT statement that runs instantly. I assume it's the select field from the table.

Any ideas what the problem could be?

SqlCommand com = new SqlCommand("LoadGUIDs", sqlconn)
{
    CommandType = CommandType.StoredProcedure
};

SqlDataReader data = com.ExecuteReader();  // << TIMES OUT

Other procedure calls from the web page work as expected.

If I comment out this section, the rest of the site loads data as expected. I've tried loading the web page from different servers as well. Same result.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
General Chad
  • 455
  • 1
  • 5
  • 17
  • Ten minutes ago? Have you done any investigation during that short time period? If so, what was the outcome? – Quality Catalyst Aug 25 '17 at 18:27
  • Could be a dead lock. You have to debug your stored proc and check out what is happening on the server to see why it would time out. You could search for "query timeout" or "query deadlock" etc. No one here can help you without you providing **a lot more** information. – Igor Aug 25 '17 at 18:28
  • Would a dead-lock cause it to time out on the C# page and not in SQL Manager? – General Chad Aug 25 '17 at 18:32
  • The only investigation I've done is to run the proc on SSMS and see the resultant data instantly. – General Chad Aug 25 '17 at 18:34
  • Use the SQL Server Profiler tool to run a trace, make sure you have the SQL batch start/end and all the Deadlock events select (unselect most of the default events like audit login/logout and other noise), and then duplicate the issue with the trace running. See if you see deadlocks in your trace. If so, it'll give you information about what is locking. Also, use "Activity Monitor" in SSMS to look for locking issues whiel it runs. – pmbAustin Aug 25 '17 at 18:38
  • What else is going on with that page near or at the same time? Deadlock would cause this and could be caused by other queries locking a resource your simple select needs. Running it by itself in SSMS wouldn't necessarily run into the deadlock problem. As others have suggested, try profiler. – n8wrl Aug 25 '17 at 18:39
  • Look at this question: https://stackoverflow.com/questions/29375905/sql-query-times-out-when-run-from-c-fast-in-sql-server-management-studio/29376470#29376470 – Joel Coehoorn Aug 25 '17 at 18:40
  • Sorry, this is on AzureSQL. No Profiler that I know of. – General Chad Aug 25 '17 at 18:40
  • Thanks Joel. I'll look at that other Q as well. - Even if I step through the code line by line, the reader times out on this line. I've never seen anything like this. – General Chad Aug 25 '17 at 18:42
  • My latch waits are less than 2ms, I have no row level locking issues or lock escalations. – General Chad Aug 25 '17 at 18:47
  • Joel, that other linked question doesn't apply here. Sorry. – General Chad Aug 25 '17 at 18:56
  • You can post the question on this forum if not possible here https://social.msdn.microsoft.com/Forums/azure/en-US/home?forum=ssdsgetstarted. I will love to work with this issue. – Alberto Morillo Aug 26 '17 at 23:04
  • Thanks Alberto. Not sure why the Stack Overflow peeps think this is a duplicate when it is clearly not. – General Chad Aug 28 '17 at 12:44

0 Answers0