Intermittent MS SQL 2005 Connection Dropping

Report · Jun 10, 2008

Recently I have been having errors with my SQL SERVER 2005 datasource dropping connection.

Here's a history of the issue:

About 3 weeks ago my queries began slowing to a crawl and timing out. After some research the hosting company had my database on an overloaded server and have since moved me to a new SQL Server. The queries now complete quickly however I am receiving intermittent errors with the SQL Server Connection Dropping. (Although I was having this issue on the previous server, but not as often as the query timeout error)

The queries and use working 99% of the time, I just receive these errors intermittently.

Here are two errors I received this morning:

"Date/Time: {ts '2008-06-10 11:09:56'}
Diagnostic: Error Executing Database Query. Timed out trying to establish connection <br>The error occurred on line 9."

"Date/Time: {ts '2008-06-10 11:10:48'}
Diagnostic: The request has exceeded the allowable time limit Tag: CFSTOREDPROC <br>The error occurred on line 9."

I don't know why these errors would be popping up all of the sudden because the stored procedure at line 9 hasn't changed for over 6 months and the error is recently appearing. The error also occurs during other random queries as well. Its not always the query at line 9.

Here is some other potential useful pieces of info:

JDBC URL: jdbc:fusionreactor:wrapper:{jdbc:macromedia:sqlserver://server-info:1433;databaseName=datasource;SelectMeth od=direct;sendStringParametersAsUnicode=false;MaxPooledStatements=1000};name=sourcename

Driver Class: com.intergral.fusionreactor.jdbc.Wrapper
Maintain Connections: Checked
Timeout (Min): 20
Interval: 7
Login Timeout: 30

I hope this is clear enough. Any ideas or previous experience with this? Any help is greatly appreciated.

Thanks!

Jimmy

Report · Jun 10, 2008

quote:

"Date/Time: {ts '2008-06-10 11:10:48'}
Diagnostic: The request has exceeded the allowable time limit Tag: CFSTOREDPROC <br>The error occurred on line 9."

This could indicate a performance issue in your stored procedure.

Check the execution plan for this stored procedure in SQL management studio and see what indexes it uses. If there are no indexes then create appropriate ones. If there are indexes check their fragmentation level and defrag or re-build them.

Report · Jun 10, 2008

Thanks for the reply! I didn't think that that SP/table would cause an issue because its a fairly simple procedure (select columnName from table where columnID = 1) in a table with less than 200 rows but I'll give anything a shot.

I did change the index to clustered from non-clustered so we'll see if that helps. Thanks!

Jimmy

Report · Jun 10, 2008

If your SP is simple my suggestion may not be applicable.

These are shots in the dark:
1. You might experimenting with unchecking the "Maintain Connections" for your database connection. I seem to remember a CF6 / SQL 2000 server setup dropping connections occasionally until I unchecked the option.

2. Try another JDBC driver, either an updated one from Adobe (if you're not using the latest) or Microsoft's JDBC driver. What version of ColdFusion are you using?

Report · Jun 10, 2008

Hi JR,

I'm on CF 8,0,0,176276 on a Windows 2003 machine. Right now I'm running the JDBC wrapper provided by FusionReactor to try to track down this issue.

After changing the index to clustered there was a 33% performance increase in the SP but it was usually very fast already. I will see if there are any errors in the next 24 hours, if so I'll take off the wrapper (although I don't think thats the issue) and if there are still errors I think I'll look into an updated JDBC.

Could there possibly be settings on the SQL Server that could be causing these issues? Thanks!

Jimmy

Report · Jun 10, 2008

quote:

Could there possibly be settings on the SQL Server that could be causing these issues?

Not that I am aware of. I'm using CF7 + SQL 2005 without problems. Does your SQL Server have the latest service pack?