question

StanleyBarnett avatar image
StanleyBarnett 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
sql-server-2012log-file-size
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
0 Likes 0 ·
This "restructure" - what do you mean? How did you make the change to the field?
0 Likes 0 ·
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?
0 Likes 0 ·
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?
0 Likes 0 ·
@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.
0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.