none
Alter Statement Filling Transaction Log File Fast

    Question

  • Hi Folks,

    I have ran below alter statement which was part of deployment but while running this tr-log file getting full quickly.

    ALTER TABLE [dbo].[MY_ORDER_LINE] ALTER COLUMN [CREATED_BY]  INT NULL ;   --This has not yet been updated – column data type is still ‘smallint’

    ALTER TABLE [dbo].[MY_ORDER_LINE] ALTER COLUMN [LAST_UPDATED_BY] INT NULL;  --  This has not yet been updated – column data type is still ‘smallint’

    ALTER TABLE [dbo].[MY_ORDER_LINE] ALTER COLUMN [LAST_UPDATE_LOGIN] INT NULL;  --  This has not yet been updated – column data type is still ‘smallint’

    GO

    SQL Server Version: 

    Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) 
    Oct 19 2012 13:38:57 
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

    OS: Windows Server 2008 R2 standard 64 bit

    RAM: 8 GB

    Log file size: 53 gb

    I have arranged the space but now this query is running from past 120 mins and not completing.

    There is no blocking also.

    Kindly advise me what to do to get it complete.

    Thursday, June 14, 2018 5:54 PM

Answers

  • These ALTER statements requires the entire table to be reloaded under the hood, because the pages need to be rewritten to accommodate the enlarged data type. (An int always takes up four bytes in a non-compressed table.)

    If this is production, I guess it is too late.
    If this is deployment to a lesser service, and Production is yet to come, there is reason to consider alternatives. One option is to create a new table with the new definition and copy data over, and copy in batches, to keep the T-Log in check. If you go this route, you will need to re-create indexes, triggers, constraints and move referencing foreigh keys. Not really a walk in the park.

    Since the colunms are nullable, you could also add then as new columns and copy data over from the existing column, and again do this in batches. Once you are done you drop the existing columns and rename the new columns to the old names. The space will be reclaimned the next the clustered index is rebuilt. This is likely to be a smoother ride than cooying all to a new table.

    When you work with batches, be careful to define your batches from the clustered index, or else you will waste a lot of time scanning the table to identify the batches.

    Thursday, June 14, 2018 9:25 PM
  • That is by design. My first advice would be to not kill it as it could be even worse. The rollback could take more time than taken by the original transaction. 

    How large is that table? Is your DB in simple recovery? (well, it doesn't matter much as it still is a single operation and won't truncate the active VLFs until the entire operation is done).

    I see two options when altering columns of huge tables with hundreds of millions of records.

    1. Either let it run for hours but ensure you don’t kill the operation in the middle. If you do, you run the risk of an even worse situation (the rollback would be much longer). Also, the log would grow dramatically so take that into account.
    2. Create a new table (schema only) as a copy of the table being worked in the same DB but with the desired column datatype. Migrate data from the old to the new table and finally rename the new one to the old to retain the name. You would have to ensure that there is enough space to accommodate the new table. There are a few things you can do to make this even faster such as, dropping the indexes on the old table prior to copying data and creating them afterward etc.

    Hope this helps.


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.


    Thursday, June 14, 2018 9:52 PM

All replies

  • Since the operations are atomic and has to be logged, then you just have to wait for it. It will take the time it takes and it will log the amount of log records that it does. Trying to cancel it will just cause a rollbackup which will take as long time as it has been processing already. This is why it is so important to these these tings in a test environment first, with realistic amounts of data.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Thursday, June 14, 2018 6:55 PM
    Moderator
  • These ALTER statements requires the entire table to be reloaded under the hood, because the pages need to be rewritten to accommodate the enlarged data type. (An int always takes up four bytes in a non-compressed table.)

    If this is production, I guess it is too late.
    If this is deployment to a lesser service, and Production is yet to come, there is reason to consider alternatives. One option is to create a new table with the new definition and copy data over, and copy in batches, to keep the T-Log in check. If you go this route, you will need to re-create indexes, triggers, constraints and move referencing foreigh keys. Not really a walk in the park.

    Since the colunms are nullable, you could also add then as new columns and copy data over from the existing column, and again do this in batches. Once you are done you drop the existing columns and rename the new columns to the old names. The space will be reclaimned the next the clustered index is rebuilt. This is likely to be a smoother ride than cooying all to a new table.

    When you work with batches, be careful to define your batches from the clustered index, or else you will waste a lot of time scanning the table to identify the batches.

    Thursday, June 14, 2018 9:25 PM
  • That is by design. My first advice would be to not kill it as it could be even worse. The rollback could take more time than taken by the original transaction. 

    How large is that table? Is your DB in simple recovery? (well, it doesn't matter much as it still is a single operation and won't truncate the active VLFs until the entire operation is done).

    I see two options when altering columns of huge tables with hundreds of millions of records.

    1. Either let it run for hours but ensure you don’t kill the operation in the middle. If you do, you run the risk of an even worse situation (the rollback would be much longer). Also, the log would grow dramatically so take that into account.
    2. Create a new table (schema only) as a copy of the table being worked in the same DB but with the desired column datatype. Migrate data from the old to the new table and finally rename the new one to the old to retain the name. You would have to ensure that there is enough space to accommodate the new table. There are a few things you can do to make this even faster such as, dropping the indexes on the old table prior to copying data and creating them afterward etc.

    Hope this helps.


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.


    Thursday, June 14, 2018 9:52 PM