Error SQL72016 Execution Timeout Expired.

Applies to: SQL Server 2017 VM on Azure, Azure SQL Database, SQL Server Management Studio v17.9.

Date created: September 23, 2019.
 

Problem Description.
 

I exported an Azure SQL Database to an Azure Storage Account, then copied the bacpac file to a local drive on a SQL Server VM and from there I tried to import it to the default SQL Server 2017 instance using SQL Server Management Studio (SSMS), but I received the following warnings and errors:

Could not import package.
Warning SQL72012: The object [TestDB_Data] exists in the target, but will not be dropped even though
you selected the 'Generate drop statements for objects that are in the target database but that are not
in the source' check box.
Warning SQL72012: The object [TestDB_Log] exists in the target, but will not be dropped even though
you selected the 'Generate drop statements for objects that are in the target database but that are not
in the source' check box.
Error SQL72016: 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:
/*
Data is bulk loaded at this point in deployment execution
*/

(Microsoft.SqlServer.Dac)

Additional information:
Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the
server is not responding. (Microsoft SQL Server)
The wait operation timed out.






As result, I was not able to import the bacpac.

Both warnings can be discarded.

The key error is Error SQL72016 execution Timeout Expired.
 

Cause.

As you can see on the image above, the error occurred when SSMS was trying to enable an index.

When importing a package, at the beginning indexes are disabled, then tables are processed, after that indexes are enabled.

On specific indexes contained on the bacpac I received the timeout error message when trying enabling them.

Workaround/Solution.

I followed these steps to workaround the issue.

1. I scripted all indexes where I received this error message and dropped them on the Azure SQL Database that was used to originate the bacpac.
2. Then I exported the Azure SQL Database as bacpac again.
3. I used SSMS to import the database again.
4. When SSMS finished to import the database successfully, I ran the script to create to the newly imported database all indexes dropped on the first step. 

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.