Huge disk space for data file and log file executing alter table

I have a big problem with the files size on my SQL Server database.

Let me put everything into context:

On an empty database I'm creating a new table:

CREATE TABLE mytable
(
    id int identity(1,1) NOT NULL, 
    description varchar(255) NOT NULL
);

=> on disk, I have datafile=8192K and logfile=8192k

I'm inserting 1'000'000 rows into my table like this:

INSERT INTO mytable(description)
    SELECT TOP (1000000) 
        'test test test test test test test test test test test'
    FROM sys.all_objects AS o1
    CROSS JOIN sys.all_objects AS o2
    CROSS JOIN sys.all_objects AS o3;

=> on disk, I have datafile=73M and logfile=376M

I'm altering the description column, changing its datatype to nvarchar:

ALTER TABLE mytable 
    ALTER COLUMN description nvarchar(255) NOT NULL;

=> on disk, I have datafile=204M and logfile=1.3G

I'm executing these ALTER SQL commands several times:

ALTER TABLE mytable 
    ALTER COLUMN description varchar(255) NOT NULL;

ALTER TABLE mytable 
    ALTER COLUMN description nvarchar(255) NOT NULL;

=> on disk, I have datafile=816M and logfile=3G

Question 1: is it normal that the database grows like this when I'm switching between 2 column's types?

Question 2: applying a Database Shrink, the log file is reset (returns to 8192K), but the datafile size still remains to 816M. Is it normal?

Are there options in a SQL Server database to do an automatic shrink or something like this? This in a test database, but in production I'm starting with a 150G database so I cannot imagine the database size after some ALTER.

I'm using SQL Server 2019.

Thanks in advance for your help.

Changing the column type from varchar to nvarchar changes how the data is stored physically on the disks. Nvarchar being unicode needs two bytes to store each character (more or less, there are exceptions but they are out of scope here) and varchar is stored as regular 8-bit data (1 byte per character). When you change the data type the data will be copied to a new page as it has to be rewritten in the new form and therefore the database will grow.

Since your table is a heap (has no clustered index) the allocation algorithm will not update the GAM but move the data to a new page and create a pointer on the old one. To reclaim the space you will need to execute ALTER TABLE mytable REBUILD or create a clustered index on the table after which you can shrink the database file.

Paul Randall has blogged a lot about SQL Server disk structures. If you are interested. Those are highly reccomended especially the Anatomy of a page post and Hugo Kornelis can tell you the story of the Table scan from hell

One thing you will discover reading this is that it is not really a good idea to shrink your database files. You can set an option on the database to auto shrink it ALTER DATABASE Empty SET AUTO_SHRINK ON but this is really not a good idea!

If you want to make an educated guess at the size of the conversion you can calculate how big the current varchar columns will be after conversion, this will not calculate the increased space indexes on these columns will take and will only be based on the max length of the column but not on the size of the data stored in it.

select 
sum(c.max_length) as current_max_length_in_varchar,
sum(c.max_length)*2 as required_max_length_in_nvarchar
from sys.tables t
inner join sys.columns c
on c.[object_id] = t.[object_id]
where t.type = 'U'
and c.system_type_id = 167 -- Type 167 is varchar, see sys.types

StackExchange Database Administrators Q#295335, answer score: 4