Lesson Learned #272: Execution Timeout Expired error message importing a Bacpac file

[Guest Jose_Manuel_Jurado]

Today, I worked on a service request that our customer got the following error message: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding importing a bacpac file, in this situation, was at the moment that SQLPackage was enabling (rebuilding) an index, for example, Enabling index 'IX_MyIndex'...

 

 

 

The first thing that we need to know that Import a bacpac using SQL Server Management Studio, SQLPackage or Azure Portal is not a restore backup. Importing bacpac means this project will perform the following steps:

 

 

 

• Create the structure based on Model.Xml.
  
• Disabling the indexes and constrains.
  
• Import the data using bulk insert.
  
• Enabling and revalidating the constrains.
  
• Enabling and rebuilding the indexes.
  

 

 

 

All these operations depending on the size, number of indexes, data to import, etcc..might consume huge resources in terms of IO and CPU, for this reason, having a correct choose of the database and parameter definition in the SqlPackage will be key for a successfully operation.

 

 

 

Other error that you could receive if the database is very busy might be: (A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The timeout period for the semaphore has expired.)

 

 

 

Also, depending the different operations to be made, for example, ALTER TABLE to add, temporal tables, you could receive this type of error: Error SQL72014: Framework Microsoft SqlClient Data Provider: Msg -2, Level 11, State 0, Line 0 Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Error SQL72045: Script execution error. The executed script: ALTER TABLE [dbo].[mytable] ADD PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

 

 

 

In this situation, our customer choose a database that was not enough to attend in timely manner these operations and any of this process took more time that the parameter /p:commandtimeout=60 that by the default SQLPackage has and for this reason they got the following error message: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

 

 

 

We have two option to bypass this issue:

 

 

 

• Change the /p:commandtimeout to a higher value to give time to the database applying the request.
  
• Choose a right database type, for example, business critical or premium where you have a good IO performance and once the process has been completed change this to the database tier that your business needs.
  

 

 

 

Additionally, if you want to see all these operations and the time taken, you could use the option of SQL Profiler of Azure Data Studio or use SQL Auditing of Azure SQL Database feature.

 

 

 

Enjoy!

 

Continue reading...