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.