Huge disk space usage increase when altering a column

I hope this question is easy for someone to answer :)

I have a table that looks like this (unimportant columns hidden)

CREATE TABLE [dbo].[Log](
    [LogID] [int] IDENTITY(1,1) NOT NULL,
    --several other columns
    [Name] [nvarchar](512) NOT NULL,
 CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED 
(
    [LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Now I did some indexing on that table and that also involved shortening the Name-column to 256 width.
But when I change this column

ALTER TABLE Log ALTER COLUMN Name NVARCHAR(256) NOT NULL
GO

The database grows by a non trivial amount. (And yes I double checked - this is the only change I make here)
In total that table has 90746 entries and before altering the table SSMS said the size was 247.56MB.

But after this update the database grew to 336.13MB.

Don't know if that matters here but SELECT @@VERSION gets Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)
Mar 29 2009 10:11:52
Copyright (c) 1988-2008 Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)


How can this be? Can someone explain?

UPDATE: I tried DBCC SHRINKDATABASE (myyDB, 0); that led to a smaller database of 268.81MB, but that is still bigger than the original database size with a wider column, I still do not understand :)

SQL Server will create a new column, copy the data over, and drop the old column. The table will increase in space by some factor larger than just the size of the new or old column and/or the average or max length of the data. The reason is that temporarily the copy of the data for all rows on a page can't possibly fit on the page, so many new pages will have to be generated.

It doesn't reclaim this space after dropping the column. In order to reclaim the space, you'll need to rebuild the table / clustered index.

StackExchange Database Administrators Q#51672, answer score: 6