2

We migrated some piece of old software to a new server. We used SQL Server 2008 Enterprise in the past and now we are using SQL Server 2014 Enterprise on a new machine, so it should be faster now.

The old software is legacy software and about to expire, therefore I don't want to put much effort in fixing it. But for some reason there is a C# function running a SQL query against the database for which I get the error message

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

All I read about that is, that I have to extend the timeout time by using CommandTimeout. But unfortunately everything runs under "context connection = true". Therefore, it would take quite a bit work to rebuild this function with an opportunity to change the timeout.

And I'm asking myself, why did this run on the old machine and way it won't on the new one. So it has to do something about the new machine or the new SQL Server engine. Is there any way to change the standard timeout of 30 seconds for a command in the .NET Framework or in the SQL Server?

Thanks a lot for any suggestions!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mr. T
  • 65
  • 1
  • 8

2 Answers2

1

You can set the timeout of a command with the CommandTimeout property:

var cmd = new SqlCommand { CommandTimeout = 60 }
Owain Williams
  • 2,387
  • 17
  • 22
  • Thanks for the answer, but `CommandTimeout` won't work while using `SqlConnection` with `context connection = true`. And I wonder why I have to change the code, because it already worked on the old machine. – Mr. T Feb 25 '16 at 09:09
0

Ok, I didn't find a sollution for the problem, yet, but the timeout is not really the source of the problem. I gained access to the old system and run some tests and it turned out that the same function on the old machine with the old server software runs a lot faster, such that there is no timeout.

Hence, I have to focus on server speed and database tuning.

Thanks to everyone who occupied himself with this question!

Edit:

I found a solution to my problem, indirectly. I couldn't find out, why the execution of the statement on the new machine takes so long. But it turned out that the statement itselft uses table variables. I changed them to a local temporary table in the database tempdb. Now the execution takes less than one second instead of more than 7 minutes!

For me, it looks like a problem with some cache or a miss-configured SQL server. Unfortunately, I'm not really the server administrator and I will not twiddle with it. But I will mention it to the administrators. At least, the program runs now perfectly.

Mr. T
  • 65
  • 1
  • 8
  • 1
    So this is a SQLCLR question? There is a difference between the two systems: SQL 2008 was linked to CLR v2.0 and .NET Framework v3.5, whereas SQL 2014 is linked to CLR 4.0 and .NET Framework 4.0 at minimum, but will use the latest version that you have installed. You need to give more specifics in the question as to what the code is doing in order to get better assistance. – Solomon Rutzky Mar 03 '16 at 15:56
  • Well, it is only a SQLCLR question indirectly, because the .NET Framework causes the timeout problem. But if I take the SQL statement from the source code and execute it in the Management Studio, directly, it takes far too long time for execution. So it really is a problem with the SQL statement (or the server) itself. But, luckily, I found a solution (as mentioned above)! But thanks for the answer, anyways! – Mr. T Mar 07 '16 at 16:50
  • Ok, if it still takes 7 minutes outside of the SQLCLR object, then yes, clearly SQLCLR isn't the issue. That info wasn't in the Question. Looking at your update, your terminology is confusing. When you say "virtual tables", do you mean Table Variables (starting with `@`)? And when you say "a user defined table in the database tempdb", do you mean a local Temporary Table (starting with `#`)? It is probably not a misconfigured SQL Server. If you posted the query in the Question, it would be easier to help determine the issue, especially now knowing what helped. – Solomon Rutzky Mar 07 '16 at 18:05