April 12, 2012 at 9:32 am
I was trying to add a new column to a table 'x' in a database 'dbx', but I failed to add the new column as it gave a time out error and I had to cancel the operation.
But one thing that I did notice was the increase in size of 'dbx.mdf' file when I was trying to add the new column.
and even though I cancelled the operation and the new column was never added, there is an increase in the size of 'dbx.mdf' file
Just wondering why is there an increase in size even though the operation is cancelled/rolled back?
What can be done to restore it normal size (prior to the operation)?
April 12, 2012 at 9:39 am
Your addition of the column probably timed out because the database needed to expand to hold the new column and it took too long. A rollback of the transaction (adding a column) isn't going to rollback the database growth.
How big is the database and what is the growth setting for it as well?
April 12, 2012 at 10:10 am
The initial size of the database was 34.373GB
When I tried to add a new column, the size increased to 36.549GB
Auto growth setting is set to 200MB unrestricted growth
April 12, 2012 at 10:16 am
Looks like it was adding at least 2GB of space when adding the column. How big is the table (rows) you were trying to add the column?
April 12, 2012 at 10:38 am
it's a big table with 1,415,718 rows.
April 12, 2012 at 11:28 am
How wide (number of characters)? How much wider with the new column? Were you adding the column at the end?
April 12, 2012 at 12:08 pm
nvarchar(400) we are trying to add it in the middle of the existing columns?
does trying to add it at the end of the columns matter?
April 12, 2012 at 1:47 pm
Thanks for asking for that question!
Discovered the problem and the solution.
Yes! there is a difference. when a new column is added at the end, it just alters the table by adding a new column.
But when we try to add it in the middle, it does more work like design changes and creates a temp table to move the data in and out, so it would take more disk space and time.
Best option is to add it at the end.
April 12, 2012 at 1:59 pm
Annee (4/12/2012)
Thanks for asking for that question!Discovered the problem and the solution.
Yes! there is a difference. when a new column is added at the end, it just alters the table by adding a new column.
But when we try to add it in the middle, it does more work like design changes and creates a temp table to move the data in and out, so it would take more disk space and time.
Best option is to add it at the end.
Glad you were able to figure out the problem and solution. One thing to remember, physical order of the columns is a human requirement, not a database requirement. Eapecially if you are adding a column to a large table.
April 12, 2012 at 3:06 pm
It sounds like you were using the Table Designer in SQL Server Management Studio to add your column. I sometimes use the Table Designer for viewing existing tables but we have more control making modifications using a Query Window instead and issuing T-SQL commands directly.
The Table Designer is convenient though, so setting up a table change using it is fine, but do not save it, use the Script button to get the T-SQL the Table Designer would run if you pressed Save. This way you can review the changes before applying them. If you did that for your latest change where you attempted to add a column to the middle of the table you would have seen that the Table Designer tried to create a staging table with your new definition (i.e. with the new column in the middle), copy all your data from the old table into the staging table, dropped the old table and then renamed the staging table have the name of the old table....al the while managing any foreign keys, indexes and triggers to make sure you end up with what you started with, plus your new column. That amounts to a lot of work on a large table, as you learned. Try out the Script button when you have a chance. It is very useful to see what the GUI tries to do in the background, and is actually a great learning tool.
As an aside, the reason why the operation never completed is because the Table Designer has a built-in timeout, where the Query Window does not. This is another reason why it safer to use the Query Window to make all changes. You can change the timeout (Tools > Options > Designers > Table and Database Designers > Transaction time-out after), but once you get to know the Script button hopefully you'll never have to worry about it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 13, 2012 at 2:46 pm
Thanks for the suggestions!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply