Exit
  • Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
  • 한국 커뮤니티
0

Intermittent MS SQL 2005 Connection Dropping

Guest
Jun 10, 2008 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
TOPICS
Database access
1.5K
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advisor ,
Jun 10, 2008 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.
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jun 10, 2008 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
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advisor ,
Jun 10, 2008 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?
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jun 10, 2008 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
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advisor ,
Jun 10, 2008 Jun 10, 2008
LATEST
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?
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources