Changing a column's data type fills the transaction log

Running this code:

ALTER TABLE npidata
ALTER COLUMN npi varchar(20)

Gives this error:


Msg 9002, Level 17, State 4, Line 2

The transaction log for database 'SalesDWH' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

I am changing the NPI column's data type from varchar(80) to varchar(20).

The following code gives me the same error message:

insert into npidata1 select * from npidata

• log_reuse_wait_desc shows NOTHING

• The recovery mode is SIMPLE

• autogrowth is set to NONE

• autoshrink is set to true

What else can I do? My understanding is the logs should just be truncating every time they get too big. What am I doing wrong?

the very first thing you have to do is to change AUTOSHRINK to false. There is absolutely no reason to ever have it true. See AUTOSHRINK: Turn it OFF!.

Both operations you are attempting require a size-of-data update in a single transaction. A single transaction requires that much log, irrelevant of the recovery model. You must increase the log size to accommodate your transaction.

StackExchange Database Administrators Q#10062, answer score: 5