What is ALTER TABLE actually doing?
-
I know what we did, I know the ending state, but I've no clue why it worked the way it did. Please fill me in--obscure know-how like this can save serious time and brainsweat.
We had to alter a nullable column from decimal (9,0) to (13,3). [This bumps it from 5 to 9 bytes storage per row.] The table was big (600,000+ rows, 161 columns, 9203 well-packed and clustered extents, with two additional indexes). We ran an ALTER TABLE...ALTER COLUMN command (flipping from 9,0 to 13,3), and after 32 minutes we got "out of space on log file". The log file had bloated up from 400M to 1.6G, filling the available space. (It's a development server, it's on full recovery mode, and the ldf's on C:. Yes, the configuration's screwy, but that's a different problem.)
We then dropped the column (ALTER TABLE...DROP <col> ) and added it (ALTER TABLE... ADD <col> ) . Both commands took sub-second time to run, with no impact on the log.
?? WHY ??
Why did changing the column cause SQL to log the activity? Why didn't dropping the column or adding the column cause mondo logging? When you drop a column, where does the data space go? When you add a column, where does the space come from? No way was the table fully modified when either of those two commands were run. The column's space had to have still been there after the drop, and the necessary additional space couldn't have been added throughout the table on the fly with the add. Why does altering the column--making it bigger--differ so drastically from the above two situations?
(I wanted my 100th post to have a deep and meaningful comment on the Human Condition, but this'll do
)Philip
-
When you alter a column containing existing data, Sql Server has to run a transactionally safe Update to convert the existing data, therefore has to log every updated row. Also, because you are changing row-size, an 'update-in-place' can't be done, so each update actually causes 2 log entries, for a delete followed by a re-insert.
Dropping and re-adding the column does not require a log entry of each of the 600K records.
-
But why not? What does the engine actually do to/with all the data pages when a column is dropped? What does it do when a column is added?
Philip
-
I remember something from an old 6.5 internals course that covered sometimng like this - although I may hav ekilled those brain cells. What is does is that is logically deletes or adds the column - a very quick process into the table structure, it does not effect each row like an alter column (the row structure). The actually physical change to the data does not occur until the data row is touched by an update of some kind.
Hop this kind of helps and I don't guarantee anything above.
-
There was a lengthy exchange on this site a few months ago regarding ensuring that security classified data is genuinely destroyed. The outcome seemed to be that SQL Server doesn't do anything in particular with it. People ran various tests and even simply overwriting a field did not necessarily wipe the original data! How much did the database size change during the operation? Do you just add the space for the increment or for a complete new column?
-
On the first attempt, we altered the column size, and on the second we dropped it and then recreated it. However, these were just commands, instructions issued to the database engine; what it actually did (that is, how it chose to put our commands into effect), we have no control over. And that's what I'm hoping to find out--what the SQL Server engine does whenever commands like these are issued.
For my money this is right and proper RDBMS behaviour. I only want to tell the box what to do, not how to do it. (That's why we pay Microsoft the big bucks.) It's just that, in this situation, I'm getting unexpected (and unappreciated) behaviour, and to master this tool (as opposed to Oracle or DB2) I'd like to know what's going on "inside the box".
Philip
-
Try running this search against the microsoft.public.sqlserver.* newsgroup hierarchy ...
http://www.google.ca/groups?num=100&hl=en&lr=&q=alter+table+log+group%3Amicrosoft.public.sqlserver.*
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply