0

I have a program in C# that connects to SQL Server 2008 R2; after upgrading to SQL Server 2017, connection timeout errors happen a lot of times.

Remember: no query or data has been changed since the update.

Any solution?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Reza Akraminejad
  • 1,412
  • 3
  • 24
  • 38
  • 1
    You forgot to post your C# code! Also, check that your connection string is still valid and that any logins from 2008 exist in 2017 – Syntax Error Feb 03 '18 at 14:56
  • Did you only upgrade the version of SQL, or did you replace the server itself? What protocols are enabled on the new server (e.g. named pipes, shared memory, etc). Do they match the old server's settings? Any clues in the event logs? Is your app on the same server as your DB? – JohnLBevan Feb 03 '18 at 15:04
  • @MrJF Well I've used using(sqlConection con=new sqlConnection( ... ) ){} and this works. The problem is connection timeouts happen a lot. – Reza Akraminejad Feb 03 '18 at 21:00
  • @JohnLBevan We installed SQL Server in new windows server edition which we have installed newly and all named pipes and protocols same as SQL 2008 R2 configured. Is there any other config which we've missed or is there any bug in new version? – Reza Akraminejad Feb 03 '18 at 21:03
  • Try working your way through these tips: https://www.brentozar.com/archive/2015/05/how-to-troubleshoot-sql-server-connection-timeouts/ – JohnLBevan Feb 03 '18 at 22:23
  • @JohnLBevan One question in article is: "Is there a pattern to the days/times of the timeouts?". Well yes. it's the time when calling SQL and traffic is high in working hours, for example from 11:00 AM to 4:00PM and another question is: "Are all queries in the application affected, or just some queries?". Yes, after timeouts we can not find queries from expensive queries, because it affect hole database – Reza Akraminejad Feb 04 '18 at 06:09
  • @JohnLBevan And this question: "During the timeouts, is the app server able to ping the SQL Server?". Should I ping from server which C# my program is installed and ping from command line? Any suggestion how to ping or Tellnet connection from 1433 port? remember it's command timeout not connection timeout. It happens after default command timeout time (30 seconds passed). – Reza Akraminejad Feb 04 '18 at 06:10
  • @JohnLBevan What's your idea? Any other suggestion? – Reza Akraminejad Feb 04 '18 at 07:32
  • You could setup a script to ping recording time & result (or simply to record a change of result; so you collect less data but all meaningful events) and leave that running for comparison. If you want to test the port as well as the availability of the server, you could use something like this: https://stackoverflow.com/questions/9566052/how-to-check-network-port-access-and-display-useful-message – JohnLBevan Feb 04 '18 at 09:51
  • I don't really have a good idea what the issue is; it's too broad to really guess at. In such situations it's best to ask a load of questions to try to narrow down where the issue lies; then you can start investigating; i.e. if you find that the server can't be pinged when you get the timeout it implies a network issue rather than the DB itself. – JohnLBevan Feb 04 '18 at 09:53
  • You found that the server has more errors during busy times... Is that because it's under heavier load (in which case, use perfmon or procmon to check resource usage), or is that because there are more attempts so it's statistically more likely (e.g. 10% of connection attempts fail; but there are 1000 attempts at midday (so 100 timeouts) vs 10 attempt at midnight (so only 1 timeout)). – JohnLBevan Feb 04 '18 at 09:54
  • @JohnLBevan From my view, it's get timeout when one timeout happens and other connections get timeout in queue. After restarting SQL server instance, it goes well, but sometimes this solution won't answer and restarting the server which SQL server is installed, all connections in normal mode and c# get answers fast. :( – Reza Akraminejad Feb 04 '18 at 10:37

0 Answers0