SQL Connection woes

[chrism]

I am getting repeated SQL timeout errors (approx 5) a day from my keepalive page (it's called on a scheduler every 15mins to keep my assemblies in memory (as advised by DASP).
It's an ASP.NET membership site, so, although the keepalive page is not in the secure area I think SQL is still involved in validating the request.

I have also received these timeouts as a result of customers using various pages directly.

Health monitoring emails me the errors and the salient part of the message is

Exception information:
Exception type: System.Data.SqlClient.SqlException
Exception message: Timeout expired. The timeout period elapsed prior to
completion of the operation or the server is not responding.

Also, today I received the following error, again as a result of a customer request - not the scheduler.

Exception information:
Exception type: System.Data.SqlClient.SqlException
Exception message: A network-related or instance-specific error occurred
while establishing a connection to SQL Server. The server was not found or was
not accessible. Verify that the instance name is correct and that SQL Server
is configured to allow remote connections. (provider: TCP Provider, error: 0 -
No such host is known.)

Mostly my site works fine. These errors are intermittent. Also I've never had them on my dev server (same code)

My web.config connection string is

<add name="OrganiserConnectionString" connectionString="Data Source=tcp:esql2k801.discountasp.net;Initial Catalog=[MyDb];User Id=[MyUser];Password=[MyPassword]"/>

I've asked support but they feel it's an application issue which is security related - can't see how really as it works fine most of the time. They suggested I post here......

Any advice much appreciated.

[Jericho]

I am getting repeated SQL timeout errors (approx 5) a day from my keepalive page (it's called on a scheduler every 15mins to keep my assemblies in memory (as advised by DASP).
It's an ASP.NET membership site, so, although the keepalive page is not in the secure area I think SQL is still involved in validating the request.

I have also received these timeouts as a result of customers using various pages directly.

Health monitoring emails me the errors and the salient part of the message is

Exception information:
Exception type: System.Data.SqlClient.SqlException
Exception message: Timeout expired. The timeout period elapsed prior to
completion of the operation or the server is not responding.


I am getting the same timeout error intermitently. I was wondering if anybody had been able to figure out what the problem is.

By the way, the SQL query my code is attempting to execute is a simple UPDATE with a WHERE clause on an indexed field. I can execute the query using SQL Server management studio and it runs fine. I'm really puzzled by what could cause this problem. I thought maybe the is a lock on the record I'm attempting to update but I don't know how I would investigate further since we have limited privileges on the database at DiscountASP.NET.

Any suggestion?

[bruce]

its most likely some kind of blocking. Is there a way to replicate the problem?

[Jericho]

It's intermitent but it occurs fairly often. So... yes I can reproduce. Do you something to suggest I should look at the next time the problem occurs?

[bruce]

I was thinking if SQL Profiling (http://discountasp.net/sp_sql-profiling.aspx) might help.

[rickasp]

Anybody found a solution yet? (I guess not) I have also the same problem.

Timeouts and 'A network-related or instance-specific error occurred while establishing a connection to SQL Server'.

Sql profiling is not an option in the current way you offer it (2 minute log)because it happens at random throughout the day, besides that, I do not think the problem is at the application.

At first I thought it was a maintanance thing, but catching the errors in the global asax now and I see how often it happens (about 8 times today).

I think this should be fixed because you can not rely on the MS SQL 2008 database hosting option at the moment.

I am on: esql2k802.discountasp.net (Initial Catalog=SQL2008_705354), using LINQ to SQL

[dmitri]

The default time out in LINQ to SQL is set to 30 seconds. The time out probably happens because your query requires to run more then this time limit. You can either improve your query or increase CommandTimeout in your DataContext. The best practice is to tune your query up for performance and as bruce said, profiler can help.

[rickasp]

The query is ok, most of the time the page (and so the query) is finished in less than a second. But sometimes it 'hangs' and can't connect to the sql server for no reason.

You should create a default.aspx page, fire some queries in it, let it run in a scheduled task every 5 minutes for 24 hours and catch the errors in the global.asax or log. I think (and hope) you will see what we mean.

[frankpatton]

I am having a very difficult time connecting to my database .. as a last resort I went to the control panel database section and did a copy and paste from the data source connection string. One thing I noticed, and I am grasping for straws here, is that the "tcp:" identifier in the front part of the string is getting dropped off of the web config when I get to the website.

Is anyone out there using vs2010 to publish their website?

below is the cut and paste followed by what ends up on the server:

any help would be appreciated:

"Data Source=tcp:sql2k803.discountasp.net;Initial Catalog=SQL2008_621130_sharedecisi;User ID=SQL2008_621130_sharedecisi_user;Password=****** ;"

<add name="SQL2008_621130_sharedecisiEntities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string="Data Source=Sql2k803.discountasp.net;Initial Catalog=SQL2008_621130_sharedecisi;Persist Security Info=True;User ID=SQL2008_621130_sharedecisi_user;Password=****** ;MultipleActiveResultSets=True"" providerName="System.Data.EntityClient" />
</connectionStrings>

[rickasp]

To come back to my post. Sorry, but the problem maybe at my side, I did some changes. I raised the Max Pool Size in my web.config to 250 (default = 100?) and do now close my DataContext (.Connection.Close()); in my code. No more errors so far, jut have to figure out which change solved it. I thought the linq datacontext connections where automatically closed (also after releasing the reference).

I am thinking of doing some SQL profiling, but can't seem to find what kind of information I get exactly from the standard template (Use to monitor general database server activity.) for example can I see which linq queries are fired and what connections stay open?

Standard template:

Audit Login
Audit Logout
ExistingConnection
RPC:Completed
SQL:BatchCompleted
SQL:BatchStarting

[frankpatton]

It appears that my login problem has nothing to do with my sql string but in using ado.net with vs2010 and silverlight .. anyone out there have any ideas, or more importantly, has anyone actually published, successfully, an ado net silverlight app ..

Help and more help

Frank

[jgmdavies]

I am also getting connection problems to a SQL Server 2008 database on esql2k801 since upgrading to .NET 4 and VS2010. I didn't change the database or the connection string used previously.

The problem is a frequent but intermittent occurrence of:

EXCEPTION: System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Are other people having problems with esql2k801 ?

Regards,
Jim

[CrystalCMS]

Are other people having problems with esql2k801 ?
Yeah me too on that server. I raised a support ticket a couple of days ago about it but the problem disappeared shortly later. Apparently support did nothing to fix the problem and it just went away. It's back now though for sure.

[CrystalCMS]

My support ticket is reopened..fingers crossed.

[CrystalCMS]

Support advised that the performance of esql2k801 has been getting hammered by a couple of customers who don't know what they're doing. A warning has been handed out and they've been given a short while to fix their problems. Hopefully this problem will go away soon.

[jgmdavies]

Thanks Joe - fingers' crossed!

(Really confusing when I'd just upgraded everything - VS / .NET / ASP.NET / ASP.NET MVC etc., etc.!!)

Just trying again - it's so slow it's effectively unavailable...

Jim

[Tornal]

Well just to add, I am using esql2k801 too and have just submitted a ticket about my web service having 504 proxy service connection timeouts. So hopefully it has got something to do with this as it has been working well for over a year previously.

[jgmdavies]

I have also created a ticket re. esql2k801.

[rickasp]

I also have timeouts / server not responding / instance-specific error occurred while establishing a connection to SQL Server errors on esql2k802. I did everything to optimize my queries. They are fast like hell most of the time, but those timeout errors X times a day are freaking me out. I have to switch host if these things are not solved.

Also created a support ticket.

p.s. for me the problems seems to be mostly in the late afternoon and evening when I check my error message times

[mjp]

Sorry about that. We are trying to get rid of a problem child or two on that box, but these are difficult problems to address. If you move someone who is causing problems to another server you're just shifting the pain to a new set of users. And we don't want to just kick someone out, so we try to get them to fix their, um, stuff.

But if they can't/won't we will certainly remove them involuntarily. Feel free to keep pestering support (http://support.discountasp.net) about it.

[jgmdavies]

esql2k801 was good earlier, but is taking forever again now (13:23 BST in UK).

Jim

[Tonky]

Sorry about that. We are trying to get rid of a problem child or two on that box, but these are difficult problems to address. If you move someone who is causing problems to another server you're just shifting the pain to a new set of users. And we don't want to just kick someone out, so we try to get them to fix their, um, stuff.


Good to know. Have you considered having one server that's kind of a "naughty chair". Sites with bad code get moved to it until they can prove they'll behave?

That way the good children aren't dragged down by the naughty ones.