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:
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:
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.