MSDeploy v3 DbFullSql CommandTimeout Bug

We use MSDeploy extensively for automating our deployments at work. We have even created custom MSBuild Targets in Nuget packages which mimic WebDeploy for handling non-Web application (Scheduled Tasks, Windows Services, etc) and SQL deployments, AppDeploy and SqlDeploy respectively. Most of these were created using MSDeploy version 2 and when version 3 was released just worked or so we thought.

Occasionally we would see timeout errors when running SQL scripts which took longer than 30 seconds to complete:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. http://go.microsoft.com/fwlink/?LinkId=178587 Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_SQL_EXECUTION_FAILURE.

We handled this early on by setting the CommandTimeout attribute on the MSDeploy destination as documented on the MSDN site.

http://technet.microsoft.com/en-us/library/dd569036(v=WS.10).aspx

This worked under version 2 of MSDeploy but lately we started seeing these errors again. Most of our scripts ran very quickly but a few took some time and would fail. We had this happen several times in the last few months but didn’t have time to research the issue deeper and instead manually deployed the scripts. This was a case of the short-term resolution was too easy compared to the time necessary to fix/research the underlying problem with a looming release deadline. After months of deferring the work we finally researched the issue.

We found only one other forum post which seems to mention this issue. It unfortunately did not have a resolution.

http://social.msdn.microsoft.com/Forums/vstudio/en-US/61c1bfb1-b0e2-4c32-8ee5-2d8603d616ef/commandtimeout-has-no-effect-after-upgrading-to-tfs-2012?forum=tfsbuild

This left us with the option to decompile MSDeploy and start spelunking into the code and try to find the cause. We used Telerik JustDecompile (great free tool!) to decompile the Microsoft.Web.Deployment dll and research the issue. After several hours of exploring found the SqlServerDatabaseProvider class (dbFullSql provider) does appear to properly apply the CommandTimeout to the SqlCommand.

                        using (SqlCommand sqlCommand = new SqlCommand(createScriptFromSource, sqlConnection))
                        {
                            sqlCommand.CommandTimeout = base.CommandTimeout;
                            sqlCommand.ExecuteNonQuery();
                        }

We compared this code across version 2 and 3 to find the code to get the CommandTimeout had changed. The SqlServerDatabaseProviderBase class handles getting the CommandTimout either the default or the explicitly provided value.

        internal static int GetCommandTimeout(DeploymentProviderSettingCollection settings)
        {
            int dBCommandTimeout = (int)DeploymentRegistryValues.DBCommandTimeout;
            int valueOrDefault = settings.GetValueOrDefault<int>("commandTimeout", dBCommandTimeout);
            if (valueOrDefault > dBCommandTimeout)
            {
                valueOrDefault = dBCommandTimeout;
            }
            return valueOrDefault;
        }

As you can see version 3 of MSDeploy appears to only utilize the explicit value when it is less than the default of 30 seconds. I believe this is a bug. As written you would never be able to use a timeout greater than 30 seconds.

Workarounds

Update default command timeout in the registry

As I was crawling through the MSDeploy code I noticed some of the default attribute values like CommandTimeout are first pulled from the registry before you using hard coded values (30 seconds in the case of CommandTimeout). Sure enough, I set the CommandTimeout value in the registry using the following command and my script ran longer than 30 seconds and deployed successfully.

reg add "HKLMSOFTWAREMicrosoftIIS ExtensionsMSDeploy3" /v DBCommandTimeout /t REG_DWORD /d 3000 /f

Set the CommandTimeout to “no limit”

Since the bug in MSDeploy on looks for values less than the default, a colleague suggested (thanks Josh) setting the CommandTimeout to no limit by using zero. Since zero is less than the default 30 seconds it would be respected and the script could take as long as its needed.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout(v=vs.110).aspx

A value of 0 indicates no limit (an attempt to execute a command will wait indefinitely).

Solution overview

I think the “no limit” workaround is preferred since it avoids a registry change on multiple build machines. I am actively working with the MSDeploy team to try to get it fixed for the next version of MSDeploy.

Please let me know if this information was helpful or you have further suggestions.