StrataFrame Forum » StrataFrame Database Deployment Toolkit » Issues » Timeout expired sqlException using the DDT with big tables

Timeout expired sqlException using the DDT with big tables

Edhy Rijo — Thu, 23 Dec 2010 13:15:38 GMT

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. :D

Here is my constructor code:
[quote]

    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

[/quote]
Here is the part of the DDT result log where this table failed the first time:
[quote]
• 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)
[/quote]

RE: Timeout expired sqlException using the DDT with big tables

Keith Chisarik — Thu, 23 Dec 2010 13:15:38 GMT

Great info.. Merry Christmas!!!

RE: Timeout expired sqlException using the DDT with big tables

Edhy Rijo — Wed, 22 Dec 2010 11:28:02 GMT

Hi Ivan, Keith,

Thanks for the response. BTW I am using MS-SQL Server 2008 Standard.

I am setting the ConnectTimeout = 0 in my code after reading the connection string and then parse it to add the ConnectTimeout parameter (see the constructor in the original posting). I am sure an overload would have make this process easier :)
I measured the time manually and it make take from 1 to 45 minutes to update this big table in some cases, that is why I set the ConnectTimeout to zero, I could try putting a value of representing an hour but since this table will just continue to grow at some point it will fail again due to timeout.

Ok, after using Ivan's best friend "Google" I found several post where while doing similar alterations to the table as mine "changing an Integer field to a Big Integer" also reported timeouts. All this is very unique to the fact that the table has 43 million records and will also be affected by any index or constrains used by the field to be altered.

After a lengthy review process I noticed that after my application crashed, the ALTER TABLE command was still running on MS-SQL monitor and had to wait until it finished to be able to try again manually via script.

Some recommendations when working with big tables are to do the following:

Remove any index or constraint
Alter the table
Re-create any index or constraint

I don't know what are the rules the DDT uses to alter the tables and would appreciate any clarification on this regard. I need to be able to trust my update process and not to put in jeopardy the customer data before an update. I know customer should be responsible for doing a backup with a table that big, but in reality that is not always the case and my application will be the one to be blame for any data lost or corruption.

RE: Timeout expired sqlException using the DDT with big tables

Ivan George Borges — Wed, 22 Dec 2010 06:46:56 GMT

You said it! ;)

RE: Timeout expired sqlException using the DDT with big tables

Keith Chisarik — Wed, 22 Dec 2010 06:45:09 GMT

Your right. A quick look in the SF source for the DataBaseMigrator shows that sometimes before ExecuteNonQuery is called the timeout is set to 0 (infinite) and sometimes it is not set at all so the default is 30 seconds, you might have to compile in debug and see which method exactly is blowing out and if it needs its timeout adjusted via an overloaded method or property.

RE: Timeout expired sqlException using the DDT with big tables

Ivan George Borges — Wed, 22 Dec 2010 06:41:51 GMT

Yep, but his issue is with the DatabaseMigrator, so I think he has no access to the SQL command during the deployment. We would probably need to have an overload or a property on the class to set the CommandTimeout to be used.

RE: Timeout expired sqlException using the DDT with big tables

Keith Chisarik — Wed, 22 Dec 2010 06:28:19 GMT

that should do it, I had the same problem with a SPROC that trims every string field in a table, below is the code

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "dbo.rtic_TrimAllFields"

'time unit below is seconds

'600 is ridiculous, unless you are trimming all the values of all the string fields in a table from a data conversion :)

cmd.CommandTimeout = 600

cmd.Parameters.Clear()

cmd.Parameters.AddWithValue("@TableName", bo.mapt_sqltable).SqlDbType = SqlDbType.NVarChar

' process the sql command

cmd.ExecuteNonQuery()

RE: Timeout expired sqlException using the DDT with big tables

Ivan George Borges — Wed, 22 Dec 2010 03:08:21 GMT

Hi Edhy.

It sounds like you need the CommandTimeout in this case. But this is set on a Command basis and not through the connection string.

So, you could try this: http://stackoverflow.com/questions/1137190/changing-the-commandtimeout-in-sql-management-studio