Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Intermittent "Internal connection fatal error" and various other errors

Posted on 2011-05-12
4
Medium Priority
?
1,315 Views
Last Modified: 2012-05-11
About two of these per hour during business hours.  About half or more are "Internal connection fatal error"; a list of others is available on request.

A SQL connection object is opened and closed (barring errors) for exactly one stored procedure call at a time.

Most of the CPU time on both the web server and the database server is spent populating work tables, exporting a Crystal Reports form to PDF, and sending the PDF to the web user as a download.  Reports with a large volume of data are more prone to error, but tiny database calls (e.g. returning all data from a single table with less than 10 rows) also sometimes run into errors.
0
Comment
Question by:jsaacson
  • 2
4 Comments
 
LVL 31

Accepted Solution

by:
MlandaT earned 2000 total points
ID: 35750530
This is probably due to high contention for database connections. You need to evaluate your strategy for opening and closing database connections - you could consider lumping together multiple sql statements into a single batch whereever possible to reduce the frequency of opening and closing new connections with the database.

Some operations like reporting may be long running and may maintain an open database connection for extended periods of time. You could consider moving reporting off onto it's own server which could run off a database replica or a warehouse.

However, can you provide a bit more light on the sort of loads we are talking about here... how many concurrent users are we talking? If it's a "lot" (and there is no formula stating exactly how many a lot actaully is) of users then maybe having a seperate report server might help. If it's not so many users, then perhaps just optimising and tuning your code, queries and data structures might help. It is actaully quite an imprecise science and depends on many different factors.

I've once had connection problems with an application which were resolved by upgrading to Microsoft Enterprise Library Data Application block.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35750929
Agree.  Crystal also has a limit on the number of simultaneous connections it will allow.  Generally it doesn't cause an error but rather the requests are queued and handled in turn but that may cause issues with other processes

mlmcc
0
 

Author Comment

by:jsaacson
ID: 35898343
Which entry (entries) in Windows Performance Monitor will let us monitor (a) the number of simultaneous database connections, (b) how long the oldest connection has been open?

Lumping together multiple SQL statements into a batch: What happens if the user aborts a request between opening and closing the connection? Should we wrap them in a Try/Catch/Finally block, where the Finally block closes the connection?

Long-running operations: We've split up one of the procedures into individual steps, to minimize wasted CPU time due to aborted requests. Sharing the load between multiple web and/or database servers is an option, albeit an expensive one. (This would involve splitting up the report load; as previously noted, the total non-report load is relatively small.)

The site has had as many as 370 distinct users per day and 83 distinct users per hour running reports. The maximum number of actually simultaneous report runs is estimated at around 20. Optimizing and tuning has already been done, speeding things up by several orders of magnitude compared to the alpha design from about a year ago.

How much does ELDA cost? How complex is it to implement? How many connections could the system handle before and after it was implemented? All database calls from our web site are already encapsulated in stored procedures.

We're making uniquely-named copies of the forms and then generating the reports from those copies. Based on a conversation with Crystal support, my understanding is that this will avoid the bottleneck.
0
 
LVL 31

Expert Comment

by:MlandaT
ID: 35900134

Lumping together multiple SQL statements into a batch: What happens if the user aborts a request between opening and closing the connection? Should we wrap them in a Try/Catch/Finally block, where the Finally block closes the connection?
Try using a Using code block. To ensure that connections are always closed, open the connection inside of a using block, as shown in the following code fragment. Doing so ensures that the connection is automatically closed when the code exits the block.
Using connection As New SqlConnection(connectionString)
    connection.Open()
    ' Do work here; connection closed on following line.
End Using

Open in new window


Long-running operations: We've split up one of the procedures into individual steps...
Ok. Cool. Though keep in mind to maintain balance between the cost of setting up and tearing down connections vs the actual operation being done in the database. Tuning a database is a very imprecise science, and many of the concerns conflict with each other (well... sort of).

The site has had as many as 370 distinct users per day and 83 distinct users per hour running reports...
Sounds like there is more reporting than anything else. Is your database structure optimised for reporting - for example, many operational databases (OLTP) are highly normalised, and this helps with keeping things consistent and write operations fast etc. However, for reporting, the numerous joins actually slow things down. So a strategy is to develop a seperate database for reporting, which is not so highly normalised. That way, queries used for reporting dont end up doing hundreds of joins, index scans and all that. Cumulatively, the time spent doing these operations could be a lot. Hence people develop a warehouse and run reporting off that.

Performance Monitor
Database Connections: http://www.wduffy.co.uk/blog/monitoring-database-connections/

ELDA... sorry. Can't help there.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Integration Management Part 2
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question