sql crash once in a while

Hello, our store is crashing and not stable, crashes every 2 days
we recently upgrade from 2.3 to 2.5
the only thing that help to restore the application is restarting the sql server

the error is:
An error occurred while updating the entries. See the inner exception for details.
   at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)

Any idea?

few extra information:
the store has many products
google find more then 100,000 pages of the store
the server is on amazon cloud, ram on the server about 7giga
the database is very big, and include 90,000 product, and about 200,000 variant
database is 20giga, and get bigger every day
we have extra of 60 giga free on that disk in amazon
database memory was set to be no more than 4.5 giga
because the store is new, we don't have many people using it yet.

we also have problem of slow page loading,
and we don't get alert when the crash happen(maybe we need to add some code for that please advise)

any help is bless



full error:
An error occurred while updating the entries. See the inner exception for details.
   at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)
   at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)
   at System.Data.Entity.Internal.InternalContext.SaveChanges()

Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute(UpdateTranslator translator, EntityConnection connection, Dictionary`2 identifierValues, List`1 generatedValues)

and sql server version: sql server 2008 R2 enterprise edition

Microsoft SQL Server Management Studio            10.50.1600.1
Microsoft Analysis Services Client Tools            10.50.1600.1
Microsoft Data Access Components (MDAC)            6.1.7601.17514
Microsoft MSXML            3.0 6.0
Microsoft Internet Explorer            9.0.8112.16421
Microsoft .NET Framework            2.0.50727.5448
Operating System            6.1.7601

and by the way this is not shrinkig of the database problem, i try that for a few times in the last week, and still it's crashing, after a while.

See last post in this thread

where is that setting of  mobile site processing
and if i close that, then what?
mobile can not buy in the store?

yaniv200 wrote:

You have a very large number of products.

I would consider upgrading the hardware that your store is running on significantly.

You should look at multiple load balanced webservers (20GB ram, 8xcore) in addition you should have a separate database server setup. Look at an SQL Cluster. You will have to look at what performance you get as you add elements to this system. You may get away with 2 webservers per DB server.

HTH

Dave

Hi dave.
this is very expensive..

http://aws.amazon.com/ec2/pricing/
http://aws.amazon.com/ec2/

one instance High-Memory Instances in amazon cost about $2.280 per Hour
or $1 per hour if you pay upfornt $1412 for reserve instance

in one month 744 hours, so for one high memory instance i need to pay: $1696
and i need 2 instances so it will be $3392

plus one DB server, maybe large could do the job,  which maybe cost about $600/$700 a month, totaly

3392+700=$4092 for one month.

do you have other solution...

by the way the bug will still happen, even that everything will work faster.

thank you. :)

Yes there is a cost attached.

However you have a lot of products & product variants.

I've been told by a ecoms server solution supplier that you should budget 10% of the total your ecommerce turn over spent on hardware infrastructure.

Not sure how true that is however we have a client with 14k products and 26k product variants using nop1.7 (not the best in terms of performance granted) we estimate a minimum of 1 8 core webserver 20GB ram and 1 8 core DB server 20GB ram infrastructure.

Can't help with the bug sorry I was commenting on your speed issue.

Realistically you can't expect a reasonable performance if you load nop (or any ecommerce solution) with 90k products and 200k product variants without giving it the hardware to cope.

HTH

Dave

yaniv200 wrote:
I am not sure if that would solve the problem, but you can test if it works in admin>configuration>general and miscellaneous setting unchek "Mobile devices supported: ". This would disable having the mobile format for mobile devices.