Could not execute batch command.[SQL: SQL not available] Timeout expired.

Problem:
The Sana Commerce database is running in SQL Azure and one or all of the scheduled Tasks like General information import or Product import fail to complete succesfully.
In the log of Sana Commerce you can find an error similar to this 
"could not execute batch command.[SQL: SQL not available] 
could not execute batch command.[SQL: SQL not available] 
Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."

Cause:
SQL Azure has different plans that offer different levels of perfomance. Sana Commerce is not aware about this and can give an error when it seems SQL Azure is not responding. In reality SQL Azure is performing at the max of its plan cap.

Applies to:
Sana Commerce 9.0

Resolution:
In the web.config of Sana Commerce you can add comment to allow you to configure SQL timeout and batchsize. By tweaking these values you can find a setting that should allow all task to run succesfully.

- Open web.config and find nhibernate’s configuration section ‘hibernate-configuration’ node.
- Add two properties to ‘session-factory’ node command_timeout and adonet.batch_size


  <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
    <session-factory>
      <property name="dialect">Sana.Commerce.Data.SanaMsSql2005Dialect, Sana.Commerce</property>
      <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
      <property name="default_schema">dbo</property>
      <property name="connection.driver_class">Sana.Commerce.Data.SanaNHibernateSqlClientDriver, Sana.Commerce</property>

      <property name="command_timeout">60</property>
      <property name="adonet.batch_size">20</property>

      <mapping assembly="Sana.Commerce.Sdk" />
    </session-factory>
  </hibernate-configuration>

The default values are 60 seconds for command timeout and 20 for batch size. But if needed with trial and error you can increase/decrease them to fit your project specific requirements.

Feedback and Knowledge Base