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)

  • 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 – Panagiotis Kanavos Apr 20 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 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 at 11:35

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, privacy policy and cookie policy

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