Content Porter generating SQL connection error

Question

I am getting below error while I am trying to content porter from my DEVtridion to TEST tridion.

Unable to save Publication (tcm:0-230-1).
Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Error Code:
0x8004033F (-2147220673)

Call stack:
System.Data.SqlClient.SqlConnection.OnError(SqlException,Boolean)
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException,Boolean)
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
System.Data.SqlClient.TdsParser.Run(RunBehavior,SqlCommand,SqlDataReader,BulkCopySimpleResultSet,TdsParserStateObject)
System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
System.Data.SqlClient.SqlDataReader.get_MetaData()
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader,RunBehavior,String)
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior,RunBehavior,Boolean,Boolean)
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior,RunBehavior,Boolean,String,DbAsyncResult)
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior,RunBehavior,Boolean,String)
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior,String)
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior)
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior)
Tridion.ContentManager.Data.AdoNet.DatabaseUtilities.ExecuteReader(StoredProcedureInvocation,CommandBehavior)
Tridion.ContentManager.Data.AdoNet.Sql.SqlDatabaseUtilities.ExecuteReader(StoredProcedureInvocation,CommandBehavior)
Tridion.ContentManager.Data.AdoNet.DatabaseUtilities.ExecuteReader(StoredProcedureInvocation)
Tridion.ContentManager.Data.AdoNet.Sql.SqlDatabaseUtilities.ExecuteReader(StoredProcedureInvocation)
Tridion.ContentManager.Data.AdoNet.ContentManagement.RepositoryDataMapper.Tridion.ContentManager.Data.ContentManagement.IRepositoryDataMapper.FindBlueprintConflicts(TcmUri,IEnumerable`1)
Tridion.ContentManager.ContentManagement.Repository.AssertBlueprintConflicts()
Tridion.ContentManager.ContentManagement.Repository.OnSaved(SaveEventArgs)
Tridion.ContentManager.CommunicationManagement.Publication.OnSaved(SaveEventArgs)
Tridion.ContentManager.IdentifiableObject.Save(SaveEventArgs)
Tridion.ContentManager.IdentifiableObject.Save()
Tridion.ContentManager.BLFacade.IdentifiableObjectFacade.Update(UserContext,String)
XMLState.Save
Publication.Save

If I see content portar logs, I can find these:

<tcm:Line ErrorCode="8004033F" Cause="true">
        <![CDATA[Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.]]>
    </tcm:Line>
    <tcm:Line ErrorCode="8004033F" Cause="false">
        <![CDATA[A database error occurred while executing Stored Procedure "EDA_PUBLICATIONS_FINDBPCONFLICTS".]]>
        <tcm:Token>EDA_PUBLICATIONS_FINDBPCONFLICTS</tcm:Token>
    </tcm:Line>

What could be the reason, why my content portar is not able to get sql connection of my TEST server, however same package is going perfectly on my LIVE Tridion.

Answer 1

Following could be the reason:

1. Health of your DB (or few tables in concern) is very low - Check the health of your Test DB by running DBCC commands and other maintenance script as mentioned in the SDL Tridion Manual for DB maintenance
2. Ports for your DB are not open - Ensure by looking at the storage config/SDL Tridion Configuration manager, what is the actual DB id is and what is the port assigned to it. If it is different than the one assigned to the Dev DB, then ensure that it is open
3. There may be issue with your SQL Server Network Configuration - Either you can compare the SQL Server Network Configuration by using SQL Server Configuration Manager for DEV DB and Test DB or you may seek help by reviewing through your DBA. Additionally, you may try running your Content Porter on the Test Server itself to check whether it works in this mode.

Answer 2

Ensure you configure these settings (absolutely necessary and from the manual):

2.4.2 Changing the timeout value for transactions

Content Porter allows users to perform a transactional import that Content Porter can roll back if it fails. As a Content Porter import can often take longer than the default system timeout setting of 30 minutes. it is recommended to change the default timeout to provide sufficient time for transactional import.

Context

Changing the timeout depends on your installed versions of Content Manager:

• On a machine which is running Content Manager Server version 2009 SP1, Content Porter uses the COM+ transaction timeout, as configured in Windows Component Services, to detect if a transaction times out. This timeout setting is set to 1800 seconds (30 minutes) by default.
• On a machine which is running Content Manager Server version 2011 SP1, Content Porter uses .NET transactions as configured in the .NET Framework. This timeout setting is set to 10 minutes by default.

On a machine which is running Content Manager Server version 2011 SP1, change the .NET system transaction as follows:

a. Navigate to your %WINDIR%\Microsoft.NET\Framework \v4.0.30319\Config folder.

b. Open machine.config in a text editor.

c. Add the following section to set the maxTimeout to 1 hour:

<configuration>
 <system.transactions>
   <machineSettings maxTimeout="01:00:00" />
 </system.transactions>
</configuration>

d. Modify the System.Transactions section by setting the allowExeDefinition attribute (from MachineOnly) to MachineToApplication:

    <sectionGroup name="system.transactions" type="System.Transactions.Configuration.TransactionsSectionGroup,
 System.Transactions, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089, Custom=null">
   <section name="defaultSettings" type="System.Transactions.Configuration.DefaultSettingsSection,
 System.Transactions, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089, Custom=null" />
      <section name="machineSettings" type="System.Transactions.Configuration.MachineSettingsSection,
 System.Transactions, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089, Custom=null"
 allowDefinition="MachineOnly" allowExeDefinition="MachineToApplication" />
</sectionGroup>

e. Reboot the server to apply changes.