geting intermittent timeout error on the DB

[GoogleCodeExporter]

What steps will reproduce the problem?
1.
2.
3.
please see attachment to see the error 

What is the expected output? What do you see instead?

What version of the product are you using? On what operating system?
2.4.1 
windows server 2008 (on shared hosting)

Website name : - http://satsang.dadabhagwan.org

Please provide any additional information below.

we are getting an error which occurs randomly, once it happens we need to 
restart the db and then problem gets resolved ...can this be due to lot of 
entries in widgetinstance and other tables as we have 

For E.g.

171470 records in aspnet_Users
2169433 records in WidgetInstance
1003547 records in WidgetZone

Out of these, majority of the entries are of guest users ....can this big data 
be a problem .. if yes how can we customize the code to not to store guest user 
and its related data ..or if there is a  specific SQL script which can delete 
guest users and its related data  ...please provide us 

If u can guess any other issue please throw light on it and guide us.

Thanks and regards,
Jinesh 

Original issue reported on code.google.com by vorajinesh on 6 Dec 2010 at 1:58

Attachments:

• error.doc

[GoogleCodeExporter]

Please post the whole exception stack trace here. Copy and paste it in a 
comment.

Original comment by omaralzabir@gmail.com on 6 Dec 2010 at 2:39

• Changed state: Started

[GoogleCodeExporter]

Also, what's your DB server config?

Original comment by omaralzabir@gmail.com on 6 Dec 2010 at 2:39

[GoogleCodeExporter]

A different topic, on the site you have a lot of viewstate. I suggest you set 
EnableViewState = False in all the widgets to save a lot of page size.

Original comment by omaralzabir@gmail.com on 6 Dec 2010 at 2:43

[GoogleCodeExporter]

Hello omaralzabir,

Thanks for your reply,

Regarding Stack Trace 
..as i told we are getting the error randomly...currently i m not getting 
error...will post stack trace as soon as i come across  

Regarding DB server 
We are using shared server...i asked hosting person and he gave me following 
details  

SQl server - SQL server 2008 R2 express edition 
RAM: 18 GB
CPU: Intel Xeon 2.27 GHZ(2 processors)
HDD: 2 TB

We have set EnableViewState = False on our test site ...its called 
http://satsangtest.dadabhagwan.org 

I would like to add few details if it can help u diagnosing the problem 

The DB size is over 1Gb and its log file(.ldf) is over 4GB can this create 
performance issue? ...will Truncating Log file improve the performance?... 
please guide us.

Regards,
Jinesh

Original comment by vorajinesh on 7 Dec 2010 at 5:48

[GoogleCodeExporter]

Hello OmaralZabir,

we came across the error ..we are getting 2 different errors randomly 
....please see the screen shots and following are the stack traces of both of 
the errors 

Stack Trace of 1.doc file 
-----------------------------------------

[SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior 
to completion of the operation or the server is not responding.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1953274
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4849707
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
   System.Data.SqlClient.SqlDataReader.get_MetaData() +96
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
   System.Data.Common.DbCommand.ExecuteReader() +12
   System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult) +975
   System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries) +113
   System.Data.Linq.SqlClient.CompiledQuery.Execute(IProvider provider, Object[] arguments) +99
   System.Data.Linq.CompiledQuery.ExecuteQuery(DataContext context, Object[] args) +195
   System.Data.Linq.CompiledQuery.Invoke(TArg0 arg0, TArg1 arg1) +102
   Dropthings.DataAccess.<>c__DisplayClass29`3.<GetQueryResult>b__28(DropthingsDataContext data) in D:\WorkingSatsang\Dropthings\DashboardDataAccess\DatabaseHelper.cs:341
   Dropthings.DataAccess.DatabaseHelper.InDataContext(SubsystemEnum subsystem, Func`2 f) in D:\WorkingSatsang\Dropthings\DashboardDataAccess\DatabaseHelper.cs:102
   Dropthings.DataAccess.DatabaseHelper.GetQueryResult(SubsystemEnum subsystem, TArg0 arg0, Func`3 func, Func`2 returnExpected, DataLoadOptions options) in D:\WorkingSatsang\Dropthings\DashboardDataAccess\DatabaseHelper.cs:338
   Dropthings.DataAccess.DatabaseHelper.GetList(SubsystemEnum subsystem, TArg0 arg0, Func`3 func, DataLoadOptions options) in D:\WorkingSatsang\Dropthings\DashboardDataAccess\DatabaseHelper.cs:463
   Dropthings.Business.Activities.WidgetActivities.GetWidgetInstancesInZoneActivity.Execute(ActivityExecutionContext executionContext) in D:\WorkingSatsang\Dropthings\DashboardBusiness\Activities\WidgetActivities\GetWidgetInstancesInZoneActivity.cs:47
   System.Workflow.ComponentModel.ActivityExecutor`1.Execute(T activity, ActivityExecutionContext executionContext) +42
   System.Workflow.ComponentModel.ActivityExecutor`1.Execute(Activity activity, ActivityExecutionContext executionContext) +49
   System.Workflow.ComponentModel.ActivityExecutorOperation.Run(IWorkflowCoreRuntime workflowCoreRuntime) +475
   System.Workflow.Runtime.Scheduler.Run() +373

[WorkflowException: Timeout expired.  The timeout period elapsed prior to 
completion of the operation or the server is not responding.]
   Dropthings.Business.Workflows.WorkflowHelper.ExecuteWorkflow(WorkflowRuntime workflowRuntime, Type workflowType, Dictionary`2 properties) in D:\WorkingSatsang\Dropthings\DashboardBusiness\Workflows\WorkflowHelper.cs:79
   Dropthings.Business.Workflows.WorkflowHelper.ExecuteWorkflow(WorkflowRuntime workflowRuntime, TRequest request) in D:\WorkingSatsang\Dropthings\DashboardBusiness\Workflows\WorkflowHelper.cs:29
   WidgetInstanceZone.LoadWidgets(Func`2 isWidgetFirstLoad) in d:\WorkingSatsang\Dropthings\Dropthings\WidgetInstanceZone.ascx.cs:55
   <>c__DisplayClass2.<SetupWidgetZones>b__1(Column column, Int32 index) in d:\WorkingSatsang\Dropthings\Dropthings\WidgetPage.ascx.cs:91
   ExtensionFunctions.Each(IList`1 list, Action`2 act) in d:\WorkingSatsang\Dropthings\Dropthings\App_Code\ExtensionFunctions.cs:60
   WidgetPage.SetupWidgetZones(Func`2 isWidgetFirstLoad, String widgetContainerPath) in d:\WorkingSatsang\Dropthings\Dropthings\WidgetPage.ascx.cs:71
   WidgetPage.LoadWidgets(Page page, Func`2 isWidgetFirstLoad, String widgetContainerPath) in d:\WorkingSatsang\Dropthings\Dropthings\WidgetPage.ascx.cs:35
   _Default.CreateChildControls() in d:\WorkingSatsang\Dropthings\Dropthings\Default.aspx.cs:159
   System.Web.UI.Control.EnsureChildControls() +87
   System.Web.UI.Control.PreRenderRecursiveInternal() +44
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +842
-------------------------------------

Stack Trace of 2.jpg file 

----------------------------------------
 [NullReferenceException: Object reference not set to an instance of an object.]
   TabPage.SetupTabs() in \\AT-SATISH01\d\WorkingSatsang\Dropthings\Dropthings\TabPage.ascx.cs:40
   TabPage.LoadTabs(List`1 pages, Page page) in \\AT-SATISH01\d\WorkingSatsang\Dropthings\Dropthings\TabPage.ascx.cs:33
   _Default.CreateChildControls() in \\AT-SATISH01\d\WorkingSatsang\Dropthings\Dropthings\Default.aspx.cs:156
   System.Web.UI.Control.EnsureChildControls() +87
   System.Web.UI.Control.PreRenderRecursiveInternal() +44
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +842

----------------------------------

Please guide us ....

Also let us know a way by which we can eliminate storing guest data on the DB.

Waiting for reply.

Regards,
Jinesh

Original comment by vorajinesh on 13 Dec 2010 at 5:42

Attachments:

• 1.doc
• 2.jpg

[GoogleCodeExporter]

Can you check if there's a non clustered index on the PageId and ColumnNo 
fields on the WidgetInstance table?

Please show me the table definition of the WidgetInstance and then show me all 
the indexes and which fields are covered by which index on WidgetInstance table.

Also note that you have a very old version of dropthings code. You should 
upgrade it to the latest code because I have made a lot of performance 
improvement on the latest code and such scalability problems were resolved long 
time back.

Original comment by omaralzabir@gmail.com on 13 Dec 2010 at 9:51

[GoogleCodeExporter]

Hello OmaralZabir,

Please find the attached excel file with table definitions ...U will get each 
table's data in the sheet with its name as sheet name.  

let me know if u need more data ...

Regarding upgrading to latest version ....we had tried doing so ..but while 
doing so we were loosing our registered user data while cleaning the DB. Please 
guide us on how can we integrate our users with new version.

Regards,
Jinesh 

Original comment by vorajinesh on 13 Dec 2010 at 11:42

Attachments:

• Table_Definitions.xls

[GoogleCodeExporter]

The spreadsheet shows there's no index on the tables except the clustered index.

Please take a look at the indexes we have on the tables in latest version of 
the database. You need to take those indexes and create them on your database. 
This will solve the timeout issue and performance problems.

Original comment by omaralzabir@gmail.com on 13 Dec 2010 at 12:01

[GoogleCodeExporter]

Hello OmaralZabir,

We have done suggested changes on the DB and we will monitor the site regularly 
for the error ..by the way can u please guide us for migrating our data with 
the latest version in place...we would like to keep our registered users data 
and get rid of guest data while migration to latest version ...

Your guidance has been of great help and will be.

waiting for reply...

Thanks & Regards,
Jinesh 

Original comment by vorajinesh on 14 Dec 2010 at 9:53

[GoogleCodeExporter]

Did you solve the migration problem?

Original comment by omaralzabir@gmail.com on 21 Feb 2011 at 2:50

[GoogleCodeExporter]

Hello OmaralZabir,

We could not migrate with our user data ....is there any way by which we can 
migarate to the latest version without loosing our user data...?

Waiting for reply..

Thanks & Regards,
Jinesh

Original comment by vorajinesh on 22 Feb 2011 at 5:42

[GoogleCodeExporter]

Original comment by omaralzabir@gmail.com on 19 Apr 2011 at 6:37

• Added labels: Type-Task
• Removed labels: Type-Defect