28 Sep 2019 0 minutes to read Contributors
When you change the datatype of (large) tables (in rows or in size) you have to consider a few things:
The alter statement will go through every record and try to restructure it to the new structure. It will need also to rebuild the indexes
In case the transaction is being rolled back when you run out of transaction log space it will take 5x the time to roll back.
An important rule to remember is that you cannot influence the operation. Once it is started 2 things can happen, 1 the operation will complete succesfully, 2 the operation will be rolled back. During the operation you can only wait and monitor your system. You will also have limited possibilities to intervene.
Because of this rule you have to prepare carefully. If you do not prepare and the operation will fail after 1 hour, you will have to wait 4-5 hours before it is rolled back.
As a database administrator we always want to be able to control the actions we perform. Therefore we take the road of least resistance and maximum control.
Instead of altering a large table do the following:
In case your table is in replication, remove it from replication, add the new table to replication without initializing (no snapshot will be created, the original subscriber table will stay in place and new records will be replicated)
This approach makes your operation a very fast one. Your new structure will be directly available. The copying of data you can control. You can do batch copies or segmented BCP out/in's
Think of it this way, the result of both operations is equal. the amount of resources, time and control significantly works in your advantage.
Big databases require a different way of thinking and planning.
In this article
Please choose a monitoring environment to connect to:
Download link successfully sent. Check your email for details.
An email with a link for creating your password is sent to