System Out of Memory Error


I am getting an error message when I try to add a column to a table(I am adding the new column in the middle of an already exist sequence of column order). The table has almost 40million rows.

Error Source: mscorlib The Error Message: Exception of the type 'System.OutOfMemoryException' was thrown

and another error message -'Unable to modify table. Timeout expired.The timeout period elapsed prior to completion of the operation

Please, I will appreciate if there is a workaround adding a column to an existing able with so much data and the added column can be added to any position in the sequence order of columns and not at the end of columns list.

I will appreciate if someone can help with this.


more ▼

asked Nov 13, 2017 at 08:22 PM in Default

avatar image

1 2

Are you trying to add the column using SQL Server Management Studio's GUI, or through T-SQL?

Nov 15, 2017 at 01:54 PM ThomasRushton ♦♦

@ThomasRushton ♦♦ I bet the OP tries to do it through SSMS GUI. It only takes to have a quick glance at the script which GUI generates to appreciate how horrendous is the exercise of attempting to accomplish it through GUI (not that there is anything wrong with it). In fact, the resources needed to make it happen make the attempt borderline dangerous to the health and even existence of the database. A number of years ago I developed a technique for the client company to accomplish the same task which allowed 50 to 100 fold improvements in performance, they are still using the technique today. It is not generic though, so I need to have the structure of the table (column list and PK specification) in order to come up with the script to add the column in the middle.

@Gehima2016 Please let me know the structure of the table (PK and defaults if any should be included) and I will come up with the script to add the column in the middle of existing columns. If this is a heap table without a unique index then you are out of luck though. Please let me know.

Nov 16, 2017 at 06:58 PM Oleg
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 13, 2017 at 08:22 PM

Seen: 38 times

Last Updated: Nov 16, 2017 at 06:58 PM

Copyright 2018 Redgate Software. Privacy Policy