Creates the new table, does the re-name swap, creates the new indexes, creates new permissions etc. This gives me a script that drops & re-creates constraints. Right click in the white space and select Generate Change Script I generated a script using the Generate Scripts option of the table designer.I changed the column size in the table designer.I’m still working on it but ran out of time before the production move. Unfortunately even using an INSERT INTO SELECT with TF 610 into an empty table I wasn’t able to get it minimally logged. Next I looked at The Data Loading Performance Guide. My initial attempt to copy the data was using an SSIS package (import wizard). Drop all of the indexes, create my new table, copy data, rename and re-create the indexes. And that one fact was enough to give me my solution.Ī variation on the second suggestion. It turns out that ~400 GB of the space used by the table are indexes (no comments please, I didn’t design it). Then I started looking at the table a bit closer. Maybe extend my log to another drive temporarily and just let the log grow as large as it needs to then clean up afterwards. Well that leaves me right back where I started. I need to make sure to script out everything from the original table (indexes, constraints and easiest to forget, permissions).I can use a minimally logged operation or load the new table a bit at a time and avoid overloading the log. There are no problems with the log however.I discounted this almost immediately because this would require at least at some point two copies of my 750 GB table.So how about the next suggestion, adding a new table and copying the data over. If everything is properly coded I shouldn’t, but I can almost guarantee that not everything is. I can’t be certain I won’t break something by moving the column. The fatal flaw with this plan (at least for me) is that this is vendor/legacy code.I can fill the new column in small/medium sized batches so that each batch goes fairly quickly and doesn’t use too much log space.Once I start putting data into the column it will actually add space to the row, potentially causing page splits.This should work because adding the new column is a schema change only so it will be almost instant and take no extra space.Here were my thoughts on the first suggestion, adding a new column. Create a new table, populate it, index it, drop the old table, re-name the new table.Add a new column to the end of the table, populate it in batches, then remove the old column.While this was going on I checked on #sqlhelp to see if anyone knew any way to minimize the log useage so my command would finish. I then tried a 200 GB log and a 350 GB log. After about an hour I got back a log full error. My first attempt was just a straight ALTER TABLE ALTER COLUMN. I can get more disk space by requesting a new disk but I want to avoid the paperwork and complications of moving some of the files to the new location.This is a test environment so I can play a bit, but I’m planning for the production change.I only have to worry about the log size of the largest single transaction. The database is in SIMPLE recovery mode so no need to take log backups during the change to manage log size.The log when I started was 50 GB with the max size also set to 50 GB.The main drive the database is on is already 2 TB in size (I’m told this is the limit for our drives but I don’t know if that’s a political or technical limit).The database has multiple filegroups each with multiple files.You might not think that would be a big deal but it ended up being a big pain. I was asked to alter a column from CHAR(2) to CHAR(4). Feel free to give me your opinion in the comments below. I will not be giving any specific examples or code. I had a task, I went through several possibilities and I’m going to share each of them and the results of those I tried. However I am filtering the data before hand based on a where statement and am a bit stuck on how to alter the table after the where condition has been satisified - or which order to do the process in? I know I will have to use timestamp::date to attain the date.Fair warning, this is a discussion piece. The new column should be added to the end and look like this: date_start I would like to add a new column called correct_date which is just the time_start column: I have the following table: id time_start area
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |