Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table with 64m rows taking 4.3 GB on disk for its data.

Each row is about 30 bytes of integer columns, plus a variable NVARCHAR(255) column for text.

I added a a NULLABLE column with data-type Datetimeoffset(0).

I then UPDATED this column for every row and made sure all new inserts place a value in this column.

Once there were no NULL entries I then ran this command to make my new field mandatory:

ALTER TABLE tblCheckResult 
ALTER COLUMN [dtoDateTime] [datetimeoffset](0) NOT NULL

The result was a HUGE growth in the transaction log size - from 6GB to over 36GB until it ran out of space!

Does anyone have any idea what on earth SQL Server 2008 R2 is doing for this simple command to result in such huge growth?

share|improve this question

migrated from stackoverflow.com Nov 29 '12 at 16:59

This question came from our site for professional and enthusiast programmers.

2  
SQL Server 2012 Enterprise adds the ability to add a NOT NULL column with a default as a metadata operation. Also see "Adding NOT NULL Columns as an Online Operation" in the documentation. –  Paul White Nov 30 '12 at 3:39
1  
One clue might be that these LOP_MODIFY_ROW entries only seem to happen for me in databases with snapshot isolation enabled. –  Martin Smith Nov 30 '12 at 12:40
2  
@MartinSmith Makes sense to me. Things often behave differently when row-versioning is enabled, usually for good reasons when you think about it. –  Paul White Nov 30 '12 at 13:02
1  
The CREATE TABLE definition for the table in question would be useful. Are any triggers active for this table? Anything else special about the database (e.g. mirroring)? –  Paul White Dec 1 '12 at 3:05
1  
And are you using any compression options? –  Martin Smith Dec 1 '12 at 9:00

5 Answers 5

up vote 26 down vote accepted

When you change a column to NOT NULL, SQL Server has to touch every single page, even if there are no NULL values. Depending on your fill factor this could actually lead to a lot of page splits. Every page that is touched, of course, has to be logged, and I suspect due to the splits that two changes may have to be logged for many pages. Since it's all done in a single pass, though, the log has to account for all of the changes so that, if you hit cancel, it knows exactly what to undo.


An example. Simple table:

DROP TABLE dbo.floob;
GO

CREATE TABLE dbo.floob
(
  id INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, 
  bar INT NULL
);

INSERT dbo.floob(bar) SELECT NULL UNION ALL SELECT 4 UNION ALL SELECT NULL;

ALTER TABLE dbo.floob ADD CONSTRAINT df DEFAULT(0) FOR bar

Now, let's look at the page details. First we need to find out what page and DB_ID we're dealing with. In my case I created a database called foo, and the DB_ID happened to be 5.

DBCC TRACEON(3604, -1);
DBCC IND('foo', 'dbo.floob', 1);
SELECT DB_ID();

The output indicated that I was interested in page 159 (the only row in DBCC IND output with PageType = 1).

Now, let's look some select page details as we step through the OP's scenario.

DBCC PAGE(5, 1, 159, 3);

enter image description here

UPDATE dbo.floob SET bar = 0 WHERE bar IS NULL;    
DBCC PAGE(5, 1, 159, 3);

enter image description here

ALTER TABLE dbo.floob ALTER COLUMN bar INT NOT NULL;
DBCC PAGE(5, 1, 159, 3);

enter image description here

Now, I don't have all the answers to this, as I am not a deep internals guy. But it's clear that - while both the update operation and the addition of the NOT NULL constraint undeniably write to the page - the latter does so in an entirely different way. It seems to actually change the structure of the record, rather than just fiddle with bits, by swapping out the nullable column for a non-nullable column. Why it has to do that, I'm not quite sure - a good question for the storage engine team, I guess. I do believe that SQL Server 2012 handles some of these scenarios a lot better, FWIW - but I have yet to do any exhaustive testing.

share|improve this answer
4  
See sqlskills.com/blogs/paul/post/… –  Martin Smith Nov 29 '12 at 17:19
1  
Just because the NULL bitmap is present doesn't mean that it doesn't change. If you run the test that Keith posted and look at the page info for the first page before and after the alter column you'll notice that there's a significant change in that a new "column" shows up named DROPPED that's null. –  cfradenburg Nov 29 '12 at 18:05
3  
@Aaron - Thanks for the fantastic, detailed reply! I had no idea SQL Server 2008 would have to touch every page. I also had no idea a NULL bitmap was stored for each page too. After much Googling I now understand why this is done. But I assumed, like in SQL Server 2012, flagging a field as NOT NULL would just add a metadata entry at the table definition level!. Even after your explanation and playing around with calcs in a spreadsheet, I still don't fully understand why the log file growth was SO huge. But I think the answer is in here somewhere. Thank you for the time. –  PapillonUK Nov 30 '12 at 12:55
1  
Not sure this actually explains anything about the logging though. The dropped column and the new column both occupy the same slot and have the same length and value so prior to this question I would have just assumed that this was a purely metadata change (just needs to validate all rows meet the NOT NULL). The before and after versions of the row are exactly the same. There is no VERSIONING_INFO in the DBCC results and I only see the logging in rows where this is updated. –  Martin Smith Nov 30 '12 at 13:24
3  
@Martin you're right, admittedly I only inspected the page changes for a single page, I did not perform a full test on a larger table to see what impact it had to the log. I believe you are right that the logging is exponentially worse under snapshot. Still, I think the changes to the page are interesting and useful even if they do not directly explain the log explosion. –  Aaron Bertrand Nov 30 '12 at 16:15

When carrying out the command

ALTER COLUMN ... NOT NULL

This seems to be implemented as an Add Column, Update, Drop Column operation.

  • A new row is inserted into sys.sysrscols to represent a new column. The status bit for 128 is set indicating the column does not allow NULLs
  • An update is carried out on every row of the table setting the new columnn value to that of the old colum value. If the "before" and "after" versions of the row are exactly the same this does not cause any thing to be written to the transaction log otherwise the update is logged.
  • The original column is marked as dropped (this is a metadata only change in sys.sysrscols. rscolid updated to a large integer and status bit 2 set on to indicated dropped)
  • The entry in sys.sysrscols for the new column is altered to give it the rscolid of the old column.

The operation which has the potential to cause lots of logging is the UPDATE of all rows in the table however that does not mean that this will always occur. If the "before" and "after" images of the row are identical then this will be treated as a non updating update and not be logged from my testing so far.

So the explanation as to why you are getting lots of logging will depend upon why exactly the "before" and "after" versions of the row are not the same.

For variable length columns stored in the FixedVar format I found that setting to NOT NULL always causes a change in the row that needs to be logged. The column count and the variable length column count both are incremented and the new column is added to the end of the variable length section duplicating the data.

datetimeoffset(0) is fixed length however and for fixed length columns stored in the FixedVar format the old and new columns both seem to be given the same slot in the fixed length data portion of the row and as they both have the same length and value the "before" and "after" versions of the row are the same. This can be seen in @Aaron's answer. Both versions of the row before and after the ALTER TABLE dbo.floob ALTER COLUMN bar INT NOT NULL; are

0x10000c00 01000000 00000000 020000

This is not logged.

Logically from my description of events the row ought in fact to be different here as the column count 02 should be increased to 03 but no such change actually happens in practice.

Some possible reasons as to why this can occur in a fixed length column are

  • If the column was originally declared as SPARSE then the new column would be stored in a different part of the row from the original causing the before and after row images to be different.
  • If you are using any of the compression options then the before and after versions of the row will be different as the column count section in the CD array is incremented.
  • On databases with one of the snapshot isolation options enabled then the versioning information in each row is updated (@SQL Kiwi points out that this can also occur in databases without SI enabled as described here).
  • There may be some previous ALTER TABLE operation that was implemented as a metadata only change and has not yet been applied to the row. For example if a new nullable variable length column was added then this is originally applied as a metadata only change and it is only actually written out to the rows when they are next updated (the writing that actually occurs in this last instance is just updates to the column count section and the NULL_BITMAP as a NULL varchar column at the end of the row doesn't take up any space)
share|improve this answer
2  
+1 Though not just with SI. Turn that off and try ALTER DATABASE TEST SET READ_COMMITTED_SNAPSHOT ON; –  Paul White Nov 30 '12 at 13:17
2  
+1 Makes a lot of sense to me. –  AlexKuznetsov Nov 30 '12 at 15:21
    
Just out of curiosity, was this test performed in 2008 R2 or 2012? –  Aaron Bertrand Nov 30 '12 at 16:58
    
@AaronBertrand - On 2008 R2 (10.50.2550.0 (X64)). Why do you see something different? –  Martin Smith Nov 30 '12 at 16:58
    
No I am just curious if you would see differences in 2012, since there were definitely changes there to make adding a NOT NULL + default column online, and maybe those changes could explain differences too. Since the OP says they are not using snapshot isolation, I'm not 100% convinced this is the reason (but maybe the OP is wrong). –  Aaron Bertrand Nov 30 '12 at 17:00

I ran the following test:

create table tblCheckResult(
        ColID   int identity
    ,   dtoDateTime Datetimeoffset(0) null
    )

 go

insert into tblCheckResult (dtoDateTime)
select getdate()
go 10000

checkpoint 

ALTER TABLE tblCheckResult 
ALTER COLUMN [dtoDateTime] [datetimeoffset](0) NOT NULL

select * from fn_dblog(null,null)

I believe that this has to do with the reserved space the log holds just in case you rollback the transaction. Look in the fn_dblog function at the 'Log Reserve' Column for the LOP_BEGIN_XACT row and see how much space it is trying to reserve.

share|improve this answer
    
If you try select * FROM fn_dblog(null, null) where AllocUnitName='dbo.tblCheckResult' AND Operation = 'LOP_MODIFY_ROW' you can see the 10000 row updates. –  Martin Smith Nov 30 '12 at 9:06
    
Wow! Is there any way to preview how much space the ALTER command will take BEFORE you actually do it? –  PapillonUK Nov 30 '12 at 12:58

The behavior for this is different in SQL Server 2012. See http://rusanu.com/2011/07/13/online-non-null-with-values-column-add-in-sql-server-11/

The number of log records generated for SQL Server 2008 R2 and below releases will be significantly higher than the number of log records for SQL Server 2012.

share|improve this answer
    
The question is why altering an existing column to NOT NULL causes logging. The change in 2012 is about adding a new NOT NULL column with a default. –  Martin Smith Dec 5 '12 at 17:21

I faced the same problem regarding a table having 200.000.000 rows. Initially I added the column nullable, then updated all the rows, and finally altered the column to NOT NULL via ALTER TABLE ALTER COLUMN statement. This resulted in two huge transactions blowing up the logfile incredibly (170 GB growth).

The fastest way I found was the following:

  1. add the column using a default value ALTER TABLE table1 ADD column1 INT NOT NULL DEFAULT (1)

  2. drop the default constraint by using dynamic SQL as the constraint has not been named before:

DECLARE @constraint_name SYSNAME, @stmt NVARCHAR(510);

SELECT @CONSTRAINT_NAME = DC.NAME FROM SYS.DEFAULT_CONSTRAINTS DC INNER JOIN SYS.COLUMNS C ON DC.PARENT_OBJECT_ID = C.OBJECT_ID AND DC.PARENT_COLUMN_ID = C.COLUMN_ID WHERE PARENT_OBJECT_ID = OBJECT_ID('table1') AND C.NAME = 'column1';

Execution time went down from > 30 minutes to 10 minutes, including replicating the changes via Transactional Replication.

I am running a SQL Server 2008 installation (SP2).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.