'Execution Timeout Expired error during DACPAC (SSDT) publish using DacServices.Deploy
We're randomly getting the below error when publishing a dacpac using DacServices.Deploy in C#. Along with tables, the Dacpac also has a post-deployment script which is the part which is raising the timeout error. We're publishing onto a new SQL Server 2014 database (hence, not an upgrade scenario). Will anyone suggest a solution please?
PS: This typically happens on the first attempt on a new server and then works perfectly on second attempt. The DacPac publish is part of our installer and installer failure makes a bad impression.
Error...
Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
We've explicitly set a timeout but that doesnt seem to be picked up because the publish fails in a little over a minute whereas the timeout is set to be 600 seconds.
DacDeployer code...
var dacOptions = new DacDeployOptions();
dacOptions.IncludeCompositeObjects = true;
dacOptions.IncludeTransactionalScripts = true;
dacOptions.GenerateSmartDefaults = true;
//Specify timeout in seconds to override the default 60
dacOptions.CommandTimeout = 600;
using (DacPackage dacpac = DacPackage.Load(dacpacName))
{
dacServiceInstance.Deploy(dacpac, DBName, upgradeExisting: true, options: dacOptions);
}
Info, Warning and Error logs returned by the event listener on DacServices.Deploy...
Initializing deployment (Start)
Initializing deployment (Complete)
Analyzing deployment plan (Start)
Analyzing deployment plan (Complete)
Updating database (Start)
Creating DW_Control...
Creating [DW_INTERNAL]...
Creating [DW_INTERNAL].[DB_VERSIONS]...
Creating [DW_INTERNAL].[SCRIPT_LOG]...
Warning! The maximum key length is 900 bytes. The index 'PK_SCRIPT_LOG' has maximum length of 1102 bytes. For some combination of large values, the insert/update operation will fail.
Creating [DW_INTERNAL].[SCRIPT_MASTER]...
Creating [DW_INTERNAL].[SCRIPT_STATUS]...
Warning! The maximum key length is 900 bytes. The index 'PK_SCRIPT_STATUS' has maximum length of 1094 bytes. For some combination of large values, the insert/update operation will fail.
Creating [dbo].[ARCHIVE_STATUS]...
Creating unnamed constraint on ...
Creating [dbo].[FK_...]...
Creating [dbo].[VW_...]...
Creating [DW_INTERNAL].[ADD]...
Creating [DW_INTERNAL].[ADD_SCRIPT]...
Creating [DW_INTERNAL].[UPGRADE]...
Creating [dbo].[VW_AMOUNT_SUMMARY].[MS_Description]...
The transacted portion of the database update succeeded.
.Net 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.
Script execution error. The executed script:
EXECUTE DW_INTERNAL.ADD_VERSION @Module = 'CTL', @ToVersion = '1.17.02.00';
EXECUTE DW_INTERNAL.ADD_SCRIPT @Module = 'CTL', @FromVersion = '0.00.00.00', @ToVersion = '1.17.02.00', @ApplyOrder = 40, @UpgrScriptFilepath = 'CTL_0.00.00.00_1.17.02.00_P_DML.sql';
EXECUTE DW_INTERNAL.ADD_SCRIPT @Module = 'CTL', @FromVersion = '0.00.00.00', @ToVersion = '1.17.02.00', @ApplyOrder = 30, @UpgrScriptFilepath = 'CTL_0.00.00.00_1.17.02.00_R_DML.sql';
EXECUTE DW_INTERNAL.ADD_VERSION @Module = 'CTL', @ToVersion = '1.17.07.00';
EXECUTE DW_INTERNAL.ADD_SCRIPT @Module = 'CTL', @FromVersion = '1.17.02.00', @ToVersion = '1.17.07.00', @ApplyOrder = 60, @UpgrScriptFilepath = 'CTL_1.17.02.00_1.17.07.00_P_DML.sql';
EXECUTE DW_INTERNAL.ADD_SCRIPT @M
An error occurred while the batch was being executed.
Updating database (Failed)
Error details: Could not deploy package.
Error SQL72014:
Stack Trace: at Microsoft.SqlServer.Dac.DeployOperation.ThrowIfErrorManagerHasErrors()
at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass14.<>c__DisplayClass16.<CreatePlanExecutionOperation>b__13()
at Microsoft.Data.Tools.Schema.Sql.Dac.OperationLogger.Capture(Action action)
at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass14.<CreatePlanExecutionOperation>b__12(Object operation, CancellationToken token)
at Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
at Microsoft.SqlServer.Dac.ReportMessageOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
at Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
at Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, DacLoggingContext loggingContext, CancellationToken cancellationToken)
at Microsoft.SqlServer.Dac.DacServices.InternalDeploy(IPackageSource packageSource, Boolean isDacpac, String targetDatabaseName, DacDeployOptions options, CancellationToken cancellationToken, DacLoggingContext loggingContext, Action`3 reportPlanOperation, Boolean executePlan)
at Microsoft.SqlServer.Dac.DacServices.Deploy(DacPackage package, String targetDatabaseName, Boolean upgradeExisting, DacDeployOptions options, Nullable`1 cancellationToken)
at DacDeployer.Program.Main(String[] args)
Stored Procedure invoked...
CREATE PROCEDURE [DW_INTERNAL].[ADD_SCRIPT]
@Module VARCHAR(30),
@FromVersion VARCHAR(20),
@ToVersion VARCHAR(20),
@ApplyOrder INT,
@UpgrScriptFilepath VARCHAR(1024)
AS
BEGIN
DECLARE @FileName VARCHAR(500);
SET @FileName = RIGHT(@UpgrScriptFilepath, CHARINDEX('\', REVERSE(@UpgrScriptFilepath)) - 1);
IF NOT EXISTS (SELECT 1 FROM [DW_INTERNAL].[SCRIPT_MASTER] WHERE [MODULE]=@Module AND [FROM_VERSION]=@FromVersion AND [TO_VERSION]=@ToVersion and RIGHT([UPGR_SCRIPT_FILEPATH], CHARINDEX('\', REVERSE([UPGR_SCRIPT_FILEPATH])) - 1) = @FileName)
BEGIN
INSERT INTO [DW_INTERNAL].[SCRIPT_MASTER]
([MODULE],
[FROM_VERSION],
[TO_VERSION],
[APPLY_ORDER],
[UPGR_SCRIPT_FILEPATH])
VALUES
(@Module,
@FromVersion,
@ToVersion,
@ApplyOrder,
@UpgrScriptFilepath)
END
ELSE
BEGIN
UPDATE [DW_INTERNAL].[SCRIPT_MASTER]
SET [APPLY_ORDER] = @ApplyOrder
WHERE [MODULE]=@Module AND [FROM_VERSION]=@FromVersion AND [TO_VERSION]=@ToVersion and [UPGR_SCRIPT_FILEPATH]=@UpgrScriptFilepath
END
END
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|