|
Group: StrataFrame Users
Posts: 2.4K,
Visits: 23K
|
I am having a timeout issue when trying to update a database which have a table with 43 million records and will continue to grow. I am using DDT 1.7.3.0 and the project is compiled for x86 with .Net 4.0 runtimes. I am using the SQL.DatabaseMigrator class and modified the constructor to be able to parse the SQL Connection string and add the ConnectTimeout=0 to avoid the timeout error. This has been working fine until now, guess that due to the constant growing of this table it keeps pushing for more time to complete any alteration to it. In this particular case I am doing the following changes to this table structure: - Alter a field from Integer to BigInt.
- Delete a field.
- Run a Post Deploy Script to add some indexes with the [INCLUDE] command which is not supported by the DDT designers yet.
The database update process is done automatically in my application. I don't have access to all customers running the application and I need this process to be able to complete the task without user intervention.
I would appreciate if anybody can take a look at the code below and give me some suggestion on what can be done to avoid the timeout error. I know that since this table will be growing constantly that this may come back to bite me. 
Here is my constructor code: Public Sub New(ByVal connectionString As String,
ByVal DDTPackageName As String,
ByVal DDTPackagePassword As String,
ByVal DataDeploymentOptions As Enumerations.DataDeploymentOptions)
Dim parseConnString As New Data.SqlClient.SqlConnectionStringBuilder
With parseConnString
.ConnectionString = connectionString
'-- Update our fields with connection values we can use later on.
_SQLServer = .DataSource
_SQLIntegratedSecurity = .IntegratedSecurity
_SQLUserName = .UserID
_SQLPassword = .Password
_SQLDatabaseNameInServer = .InitialCatalog ' Make sure to update the database being used in the connection.
'==========================================================================
'-- The following properties below needs to be reset to avoid getting error
' when creating the database the first time.
'==========================================================================
'-- This connection is also used when creating the database at installation time and if the
' name of the database is specified in the connection this will fail, so we need to blank out
' the InitialCatalog so when the connection is used, does not try to connect to a database that does not exist.
.InitialCatalog = ""
.ConnectTimeout = 0 ' This is needed when working with large datasets to avoid a timeout exception.
.AsynchronousProcessing = False ' This option is found in the SF connection string as True which generated thread errors.
' OK Sample: Data Source=PROGYTECH-XPS; Initial Catalog=MyDatabaseName; User ID=MyUserID; Password=MyPassword; Asynchronous Processing=False;Connect Timeout=0
' Bad Sample: Data Source=PROGYTECH-XPS; Initial Catalog=MyDatabaseName; User ID=MyUserID; Password=MyPassword; Asynchronous Processing=True
End With
_ConnectionString = parseConnString.ConnectionString
_DDTPackageName = DDTPackageName
_DDTPackagePassword = DDTPackagePassword
_DDTPackagePathAndName = IO.Path.Combine(_PackagePath, _DDTPackageName)
_DataDeploymentOptions = DataDeploymentOptions
'-- To handle different database name, we must use the exact database name in the DDT profile
_DatabaseNames = New DatabaseTargetName(_SQLDatabaseNameInDDTProfile)
_DatabaseNames.NameOnServer = _SQLDatabaseNameInServer
' This call is required by the Windows Form Designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
End Sub
Here is the part of the DDT result log where this table failed the first time:
• 12/22/2010 1:14:38 AM -> The table 'TransactionItems' was successfully altered.
• 12/22/2010 1:14:40 AM -> The table 'TransactionItemsImport' is being prepared to be modified.
X 12/22/2010 1:24:46 AM -> Altering table 'TransactionItemsImport' failed.
X 12/22/2010 1:24:46 AM -> FailedOperationException
X 12/22/2010 1:24:46 AM -> Alter failed for Table 'dbo.TransactionItemsImport'.
X 12/22/2010 1:24:46 AM -> ExecutionFailureException
X 12/22/2010 1:24:46 AM -> An exception occurred while executing a Transact-SQL statement or batch.
X 12/22/2010 1:24:46 AM -> SqlException
X 12/22/2010 1:24:46 AM -> Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
X 12/22/2010 1:24:46 AM ->
X 12/22/2010 1:24:46 AM -> Source : Microsoft.SqlServer.Smo
X 12/22/2010 1:24:46 AM ->
X 12/22/2010 1:24:46 AM -> Stack Trace:
X 12/22/2010 1:24:46 AM -> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
X 12/22/2010 1:24:46 AM -> at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
X 12/22/2010 1:24:46 AM -> at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
X 12/22/2010 1:24:46 AM -> at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
X 12/22/2010 1:24:46 AM -> at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
X 12/22/2010 1:24:46 AM -> at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
X 12/22/2010 1:24:46 AM -> at System.Data.SqlClient.TdsParserStateObject.ReadByte()
X 12/22/2010 1:24:46 AM -> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
X 12/22/2010 1:24:46 AM -> at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
X 12/22/2010 1:24:46 AM -> at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
X 12/22/2010 1:24:46 AM -> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
X 12/22/2010 1:24:46 AM -> at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
X 12/22/2010 1:24:46 AM -> at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
X 12/22/2010 1:24:46 AM -> at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
X 12/22/2010 1:24:46 AM -> at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries, Boolean includeDbContext)
X 12/22/2010 1:24:46 AM -> at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImplFinish(StringCollection alterQuery, ScriptingOptions so)
X 12/22/2010 1:24:46 AM -> at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImplWorker()
X 12/22/2010 1:24:46 AM -> at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImpl()
X 12/22/2010 1:24:46 AM -> at Microsoft.SqlServer.Management.Smo.Table.Alter()
X 12/22/2010 1:24:46 AM -> at MicroFour.StrataFrame.DBEngine.SQL.DatabaseMigrator.AlterSmoObject(IAlterable ObjectToAlter, String ObjectName, DBEObjectType ObjectType)
Edhy Rijo
|