Excessive logging pre-SQL 2012 when changing variable length columns from NULL to NOT NULL

In pre-SQL Server 2012 versions you may encounter excessive transaction logging when you use the ALTER TABLE ALTER COLUMN command to change NULL to NOT NULL. Depending on the record size the amount of transaction log space used could be over three times the size of the table you are altering. This post shows that this is specifically an issue when changing variable length columns from NULL to NOT NULL. The issue does not occur for the same change applied to fixed length columns.

The basis of this post is this StackExchange thread: http://dba.stackexchange.com/questions/29522/why-does-alter-column-to-not-null-cause-massive-log-file-growth

The thread pretty much gives the explanation to the problem but the point about fixed versus variable length is not entirely clear. Here’s a test script which explains the differences:

IF OBJECT_ID('dbo.testalternotnull') IS NOT NULL DROP TABLE dbo.testalternotnull;

CREATE TABLE dbo.testalternotnull
(
id int NOT NULL,
col varchar(3) NULL
);
GO

INSERT INTO dbo.testalternotnull VALUES (1,'abc')
GO 10

CHECKPOINT;

ALTER TABLE dbo.testalternotnull ALTER COLUMN col varchar(3) NOT NULL;

SELECT * FROM fn_dblog(null,null);

You can see the ten LOP_MODIFY_ROW entries where the AllocUnitName is dbo.testalternotnull

Also note the log reserve figures.

Now let’s check the case where we are changing from NOT NULL to NULL:

IF OBJECT_ID('dbo.testalternotnull') IS NOT NULL DROP TABLE dbo.testalternotnull;

CREATE TABLE dbo.testalternotnull
(
id int NOT NULL,
col varchar(3) NOT NULL
);
GO

INSERT INTO dbo.testalternotnull VALUES (1,'abc')
GO 10

CHECKPOINT;

ALTER TABLE dbo.testalternotnull ALTER COLUMN col varchar(3) NULL;

SELECT * FROM fn_dblog(null,null);

No reference to the table name here so this is just a metadata change.

Now let’s check the results for a fixed length column changing NULL to NOT NULL:

IF OBJECT_ID('dbo.testalternotnull') IS NOT NULL DROP TABLE dbo.testalternotnull;

CREATE TABLE dbo.testalternotnull
(
id int NOT NULL,
col char(3) NULL
);
GO

INSERT INTO dbo.testalternotnull VALUES (1,'abc')
GO 10

CHECKPOINT;

ALTER TABLE dbo.testalternotnull ALTER COLUMN col char(3) NOT NULL;

SELECT * FROM fn_dblog(null,null);

No references to LOP_MODIFY_ROW where AllocUnitName is dbo.testalternotnull so for fixed length columns this is a metadata only change.

I won’t show the output for changing a CHAR(3) NOT NULL to NULL as the result is the same as that for VARCHAR(3) NOT NULL to NULL. That is, it is a metadata only change.

What about BIT columns ? Well given they are represented in the record layout as a byte packed structure they can be considered to be a variable type. Let’s check this by showing the output for changing a BIT NULL to a BIT NOT NULL:

IF OBJECT_ID('dbo.testalternotnull') IS NOT NULL DROP TABLE dbo.testalternotnull;

CREATE TABLE dbo.testalternotnull
(
id int NOT NULL,
col BIT NULL
);
GO

INSERT INTO dbo.testalternotnull VALUES (1,0)
GO 10

CHECKPOINT;

ALTER TABLE dbo.testalternotnull ALTER COLUMN col BIT NOT NULL;

SELECT * FROM fn_dblog(null,null);

So, yes, you can consider the BIT type to be a variable type and changing it from NULL to NOT NULL can result in high transaction log use.

In summary, this post shows examples of high, and what you may consider excessive, transaction log use when changing variable length columns from NULL to NOT NULL. The StackExchange thread referred to at the start of this post shows that the resulting size of the transaction log may be many times the size of the table being altered. Fortunately, for SQL Server 2012 and above most column changes appear to be metadata only changes.