SQL timeout on users - Custom Fields

Hi DW.

We have a large solution with a lot of users in the AccessUser table.

We are running DW version 9.14.9

Before going to production with a new feature, we wanted to add a custom field, for all the users.
However, the action timed out during the action where it updated the users from the backend web interface.

I got this error: (Seems to be the same as this one)

Win32Exception (0x80004005): The wait operation timed out]
[SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Operation cancelled by user.]

   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +3321580

   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +736

   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4454

   System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) +1293

   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) +421

   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +380

   Dynamicweb.Data.Database.ExecuteNonQuery(IDbCommand command) +515

   Dynamicweb.Data.Database.ExecuteNonQuery(CommandBuilder commandBuilder, IDbConnection connection, IDbTransaction transaction, Int32 commandTimeout) +96

   Dynamicweb.Data.Database.ExecuteNonQuery(CommandBuilder commandBuilder, IDbConnection connection, Boolean useTransaction, Int32 commandTimeout) +188

   Dynamicweb.Data.Database.ExecuteNonQuery(CommandBuilder commandBuilder, Boolean useTransaction, Int32 commandTimeout) +58

   Dynamicweb.Security.UserManagement.Common.CustomFields.CustomFieldRepository.CreateCustomFieldColumn(CustomField field) +362

   Dynamicweb.Security.UserManagement.Common.CustomFields.CustomFieldRepository.Save(CustomField field) +36

   Dynamicweb.Security.UserManagement.Common.CustomFields.CustomField.Save() +30

   Dynamicweb.Admin.CustomFields.CustomFieldEdit.SaveCustomFields() +1947

   Dynamicweb.Admin.CustomFields.CustomFieldEdit.RequestValues() +598

   Dynamicweb.Admin.CustomFields.CustomFieldEdit.Page_Load(Object sender, EventArgs e) +440

   System.Web.UI.Control.OnLoad(EventArgs e) +106

   System.Web.UI.Control.LoadRecursive() +68

   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3785

Using this thread I tried the following:
We then tried to increase the timeout by first increasing the “/GLOBALSETTINGS/GLOBALSETTINGS/SYSTEM/DATABASE/COMMANDTIMEOUT”
but that did not help.

Then we tried to construct the “/GLOBALSETTINGS/SYSTEM/DATABASE/CONNECTIONSTRING” our self.
That worked for other actions, but not when editing a custom field. I know it worked for other situations because
when I changed to a host to a wrong one, the timeout I provided was used before it could not connect.

It's possible to mimic this behavior by locking the table:

I used this script, where I locked the table by running the first 3 lines, and released by running the last line

BEGIN TRANSACTION
UPDATE AccessUser WITH(TABLOCK)
Set AccessUserZip = 9999

ROLLBACK TRANSACTION

As far as data persisted, we could see that a column was added to the AccessUser table, and a row was added to the CustomField table.

We can add those manually by coping from a non-production environment, but that seems a bit risky to me, since we don’t know if anything else happens.

Is there a way to work around this problem that I haven’t looked at?

Developer forum

SQL timeout on users - Custom Fields

Replies