How do I deploy column changes on a large table with a lot of indexes without timing out?

We use DevOps with Change Automation to deploy changes to our environments. I have a 77GB table with a lot of indexes and we need to alter the table to add a column. This change needs to be deployed every morning for the foreseeable future, but the problem is that it times out recreating the indexes. Does SQL Server normally drop and recreate each index on a table when adding a new column?

In any case, I've set the query batch timeout on the DeployFromPackage task to 10 minutes and its still not enough time. I suppose I could set it to longer, but was curious if anyone else has seen this and how they get around it or otherwise manage it?

Below is a snippet from the error log just showing it dropping and recreating things.<br>

2020-10-14T13:33:03.5959630Z Applying update script to database 'DB' on server 'Dev'.
2020-10-14T13:33:03.8176815Z Dropping statistics [stat_Table_14] from [dbo].[Table]
2020-10-14T13:33:03.8565663Z Dropping statistics [stat_Table_03] from [dbo].[Table]
2020-10-14T13:33:03.9093297Z Dropping statistics [stat_Table_16] from [dbo].[Table]
2020-10-14T13:33:03.9499088Z Dropping statistics [stat_Table_06] from [dbo].[Table]
2020-10-14T13:33:04.0064688Z Dropping statistics [stat_Table_09] from [dbo].[Table]
2020-10-14T13:33:04.0504294Z Dropping statistics [stat_Table_43] from [dbo].[Table]
2020-10-14T13:33:04.1269558Z Dropping statistics [stat_Table_11] from [dbo].[Table]
2020-10-14T13:33:04.2189186Z Dropping statistics [stat_Table_33] from [dbo].[Table]
2020-10-14T13:33:04.3114963Z Dropping statistics [stat_Table_19] from [dbo].[Table]
2020-10-14T13:33:04.3902168Z Dropping statistics [stat_Table_38] from [dbo].[Table]
2020-10-14T13:33:04.4788101Z Dropping statistics [stat_Table_35] from [dbo].[Table]
2020-10-14T13:33:04.5898339Z Dropping statistics [stat_Table_01] from [dbo].[Table]
2020-10-14T13:33:04.6696427Z Dropping statistics [stat_Table_31] from [dbo].[Table]
2020-10-14T13:33:04.7572037Z Dropping statistics [stat_Table_37] from [dbo].[Table]
2020-10-14T13:33:04.8472469Z Dropping statistics [stat_Table_46] from [dbo].[Table]
2020-10-14T13:33:04.9301287Z Dropping statistics [stat_Table_40] from [dbo].[Table]
2020-10-14T13:33:05.0211410Z Dropping statistics [stat_Table_21] from [dbo].[Table]
2020-10-14T13:33:05.1006738Z Dropping statistics [stat_Table_25] from [dbo].[Table]
2020-10-14T13:33:05.1899532Z Dropping statistics [stat_Table_15] from [dbo].[Table]
2020-10-14T13:33:05.2871784Z Dropping statistics [stat_Table_27] from [dbo].[Table]
2020-10-14T13:33:05.3698782Z Dropping statistics [stat_Table_07] from [dbo].[Table]
2020-10-14T13:33:05.4398476Z Dropping statistics [stat_Table_08] from [dbo].[Table]
2020-10-14T13:33:05.5311178Z Dropping statistics [stat_Table_23] from [dbo].[Table]
2020-10-14T13:33:05.6191336Z Dropping statistics [stat_Table_05] from [dbo].[Table]
2020-10-14T13:33:05.7180587Z Dropping statistics [stat_Table_42] from [dbo].[Table]
2020-10-14T13:33:05.8155553Z Dropping statistics [stat_Table_36] from [dbo].[Table]
2020-10-14T13:33:05.9099627Z Dropping statistics [stat_Table_12] from [dbo].[Table]
2020-10-14T13:33:05.9930441Z Dropping statistics [stat_Table_24] from [dbo].[Table]
2020-10-14T13:33:06.0845154Z Dropping statistics [stat_Table_29] from [dbo].[Table]
2020-10-14T13:33:06.1759383Z Dropping statistics [stat_Table_10] from [dbo].[Table]
2020-10-14T13:33:06.2670889Z Dropping statistics [stat_Table_17] from [dbo].[Table]
2020-10-14T13:33:06.3589632Z Dropping statistics [stat_Table_04] from [dbo].[Table]
2020-10-14T13:33:06.4605879Z Dropping statistics [stat_Table_28] from [dbo].[Table]
2020-10-14T13:33:06.5612772Z Dropping statistics [stat_Table_18] from [dbo].[Table]
2020-10-14T13:33:06.6649667Z Dropping statistics [stat_Table_02] from [dbo].[Table]
2020-10-14T13:33:06.7658407Z Dropping statistics [stat_Table_30] from [dbo].[Table]
2020-10-14T13:33:06.8609904Z Dropping statistics [stat_Table_32] from [dbo].[Table]
2020-10-14T13:33:06.9527164Z Dropping statistics [stat_Table_20] from [dbo].[Table]
2020-10-14T13:33:07.0407926Z Dropping statistics [stat_Table_45] from [dbo].[Table]
2020-10-14T13:33:07.1270033Z Dropping statistics [stat_Table_22] from [dbo].[Table]
2020-10-14T13:33:07.2116552Z Dropping statistics [stat_Table_41] from [dbo].[Table]
2020-10-14T13:33:07.2975507Z Dropping statistics [stat_Table_44] from [dbo].[Table]
2020-10-14T13:33:07.3905417Z Dropping statistics [stat_Table_13] from [dbo].[Table]
2020-10-14T13:33:07.4938588Z Dropping statistics [stat_Table_26] from [dbo].[Table]
2020-10-14T13:33:07.5790502Z Dropping statistics [stat_Table_39] from [dbo].[Table]
2020-10-14T13:33:07.6699894Z Dropping statistics [stat_Table_34] from [dbo].[Table]
2020-10-14T13:33:07.7605656Z Dropping statistics [stat_AnotherTable_Table_01] from [dbo].[AnotherTable_Table]
2020-10-14T13:33:07.8580172Z Dropping statistics [stat_AnotherTable_Table_02] from [dbo].[AnotherTable_Table]
2020-10-14T13:33:07.9596275Z Dropping statistics [stat_AnotherTable_Table_03] from [dbo].[AnotherTable_Table]
2020-10-14T13:33:08.0579238Z Dropping statistics [stat_AnotherTable_Table_04] from [dbo].[AnotherTable_Table]
2020-10-14T13:33:08.1523554Z Dropping index [Index_Table_Covering_Include2] from [dbo].[Table]
2020-10-14T13:33:08.2536450Z Dropping [dbo].[vView1]
2020-10-14T13:33:08.3511878Z Creating [dbo].[vView2]
2020-10-14T13:33:08.4500030Z Altering [dbo].[SP1]
2020-10-14T13:33:08.5384135Z Altering [dbo].[SP2]
2020-10-14T13:33:08.6296766Z Creating index [Index_Table_Covering_Include2] on [dbo].[Table]
2020-10-14T13:43:09.9128538Z ##[warning]The error 'Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.' occurred when executing the following SQL:


Sign In or Register to comment.