1

I am trying to run the following DDL

ALTER TABLE [dbo].[table_name] ALTER COLUMN [Value] [float] NOT NULL

This generates a large amount of log file data, and there is not enough space for it on the server.

Is it possible to somehow reduce the information being logged?

I'm using Microsoft SQL Server 2016 (SP1-CU15) (KB4495257)

3
  • 4
    Find more space. You're trying to modify every row in that table, which means every row has to be written to the log to allow rollbacks if something goes wrong like ... running out of space. Or you cancel the query, it times out, the server goes down etc. If you haven't taken a transaction log backup for a long time the transaction log is probably far bigger than it needs to be Apr 20, 2021 at 11:32
  • 1
    You can't reduce required log space (SQL server needs to store entire table there), but (depend on drive configuration) you sometimes can add another log file (ldf) to another disk; in virtual environments you may be able just to increase drive size (temporarily).
    – Arvo
    Apr 20, 2021 at 11:32
  • 3
    If your db recovery model is SIMPLE or BULK_LOGGED, you could create a new table as a minimally logged operation using SELECT...INTO, coercing the NOT NULL with ISNULL(value, 0) AS Value. Then drop the ooriginal table and recreated indexes/constraiants.
    – Dan Guzman
    Apr 20, 2021 at 11:35

1 Answer 1

3

you might find it faster to create a new table, truncate the old, and then re-insert:

select t.*
into temp_my_table
from [dbo].[table_name] t;

truncate table [dbo].[table_name];  -- back it up first!

alter table [dbo].[table_name] alter column [Value] [float] NOT NULL

insert into [dbo].[table_name]
select t.*
from temp_my_table t;

You may have to pay attention if you have insert triggers or identity columns.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.