SQL errors "General Network Error" or "Cannot Connect to SQL Server" and SQL Optimizing

There are many reasons why you could see these types of errors as they are very generic and occur anytime the communication link is broken between the requesting application and server. Below is a breakdown of each error along with tips on how to optimize your code in order to reduce the chances of seeing one.

CrystalTech configures the SQL Servers to discard any SQL Query that exceeds a 30 second run time. This is done so that people who write bad queries do not affect server performance. The SQL Servers process no more than a 50% CPU average, allowing for spikes during peak times or for unusual circumstances. These servers are monitored by many tools on a 24 hour basis, keeping track of all of the Stored Procedures used and all queries customers make to their SQL databases. Stored Procedures and queries are monitored to see how long they run, the type of query made, etc. so that customers can be contacted if they are causing significant issues on the server.

The "Cannot Connect to SQL Server" error is an issue that cannot be solved by the end user. When this is found, either SQL Server has been disabled for some reason (installation of service packs, hot fixes, etc.) or the server is being rebooted. CrystalTech does not leave any servers on its network over 15 days without being rebooted. This is very important and a different approach than most companies take. However, there is a very important reason for this: As a server is running, it can become corrupt, yet still function properly. If a server runs for 45 days and, after being rebooted, does not actually boot because of a corruption, you must look at restoring data to the server. CrystalTech keeps 3 weeks of backups on site, and anything beyond 3 weeks would be absolutely useless for customers as the data would be too obsolete. To make sure data loss never occurs beyond the backup duration, CrystalTech reboots all servers on its network if the reboot period exceeds the 15 day timeframe. Reboots are done during off hours, and are done with the purpose of achieving ultimate uptime and the ability to have restores for any and all systems on our network.

If you are receiving the "General Network Error" or are curious about optimizing your SQL calls you will find the following tips helpful:

1. One of the most common issues is connections that are not closing to SQL Server. Not closing connections will cause the IIS/ASP engine or the CF engine, etc. to start disconnecting sessions. Please use the SQL Connections tool in the Control Center to see if you have any open connections and their runtime. This is a proprietary tool from the menu item DATABASE select your version of SQL and click on SQL CONNECTIONS.

2. Use SQL Query Analyzer on our servers to see how long the query takes or Profiler on your test machines to see where your bottlenecks are. If your queries are getting close to 30 seconds, then, depending on the usage of the SQL Server at the time, they could exceed our 30 second limits. Because of this, the query will be terminated and produce the error above.

3. Be sure to set INDEX on fields that you will be searching often. Times can be reduced 500% by properly indexing tables/fields

4. Be sure not to overuse the LIKE command and the ‘%’ options. These take the longest time to execute and, if combined with non-indexed fields, you will be putting a heavy strain on the SQL Server.

Add Feedback