asked
Where is the file growth happening and what sequence is things happening
Hi, Where is the file growth happening and what sequence is things happening when restructuring a large 400gb database? Using MSSQL 2012 where the mdf is on drive_M, the log is on drive_L and tempdb is on Drive_T. Now lets add a new field, or modify an existing one and let it restructure itself... 1. please explain what happens and when they happen when doing the restructure, 2. what drives starts filling up while restructuring, and when is it released (part of question #1), 3. is there any logging going on while restructuring, (does the log drive fill up), 4. With a 400gb mdf, what would be a good starting point for the drive's size?, Thanks, Stanley
Hi, I just started a restructure by changing a field from char(1) to char(2) and it is now restructuring. While watching the files (mdf, log, and tempdb) the only one that is growing is the log file. I did not expect that as this is not really a transaction, instead a restructure. I actually expected that tempdb would be the one that grew during the restructure, however it is not growing at all... Any idea where this whole process is documented? I'd like to better understand what is going on in the background as mssql does these sort of tasks... Thanks, Stanley
This "restructure" - what do you mean? How did you make the change to the field?
Thomas, I made the change using SSMS where I made a simple change of char(1) to char(2). Just enough to trigger a restructure. This restructure is actually altering the field's length. Wonder where the docs are that explains all that is going on in the background as mssql goes about its work in different restructure senerios?
Thomas, After reviewing your question( How did you make the change to the field?), a 2nd time, I am wondering why would it matter on how the change was made? Wouldn't the result and process be the same? If not, what is the determining factor?
@StanleyBarnett , Like I said on the same question over on
SQLServerCentral.com, if you were to post the CREATE TABLE, CREATE INDEXes, and any CREATEs for constraints, I believe I can show you how to tackle this problem with little or no growth of either the MDF or LDF file and, possibly, make it all work a bit faster.
18 People are following this question.
