Alter Column on Large Table 40+ million rows - Timeout Error
smcleod
Posts: 5
I'm trying to alter a decimal column from (18, 1) to (18, 2), and in order to do this the SQL Compare 7 tool is producing a script that first drop all indexes and statistics on the current table, creates a temp table, copies over data from current to temp table, drops current, and lastly renames temp table to original.
Pretty straight forward, except for the fact that it times out on the first part when trying to drop the indexes on the current/original table. The database is set to Simple Recovery mode, so I'm assuming limited logging is going on.
Is there a way to increase the timeout period??
Database size is approx 100GB in size, and there is 700GB free space on hard drive.
The error received is below:
The following error message was returned from the SQL Server:
[-2] Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The following SQL command caused the error:
ALTER TABLE [dbo].[OrdersSnapshot] DROP CONSTRAINT [PK_OrdersSnapshot]
The following messages were returned from the SQL Server:
[0] Dropping constraints from [dbo].[OrdersSnapshot]
Pretty straight forward, except for the fact that it times out on the first part when trying to drop the indexes on the current/original table. The database is set to Simple Recovery mode, so I'm assuming limited logging is going on.
Is there a way to increase the timeout period??
Database size is approx 100GB in size, and there is 700GB free space on hard drive.
The error received is below:
The following error message was returned from the SQL Server:
[-2] Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The following SQL command caused the error:
ALTER TABLE [dbo].[OrdersSnapshot] DROP CONSTRAINT [PK_OrdersSnapshot]
The following messages were returned from the SQL Server:
[0] Dropping constraints from [dbo].[OrdersSnapshot]
Comments
The query timeout of SQL Compare is hard-coded at ten minutes.
I think that you may have to manually change the column datatype in the target database in SSMS before running the synchronization. Of course the operation could also timeout in SSMS, too.