Changing value of "Initial Size (MB)"

Question

Changing value of "Initial Size (MB)"

carloarango@hotmail.com

SSChasing Mays

Points: 628
More actions
December 5, 2007 at 1:04 pm

#177229

I would like to increase the initial size of a SQL 2005 DB from 150 to 250 GB to prevent automatic autogrowth; would this have any impact in production if you do it on the fly?
Thanks,
Carlos

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

It will force the DB to grow right then and there to 250GB... You're going to want to do this during some down time...

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

carloarango@hotmail.com SSChasing Mays Points: 628 More actions · Answer 2

we have an equallogic iSCSI SAN and a 64 bit quad processor server. How much downtime should I plan for?

Thanks,

carlos

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 3

There would be little effect in performance but no one will be kicked off or users can still be logged on to the app with not much affect. Actual time would be less than a minute for files to grow.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Oh it probably won't take a LOT of time, but it should be really noticeable for a while (20-40 minutes depending on your data subsystem from my guesstimates, longer if there's competing activity). It has to allocate the space, then writes a bunch of 0's to it, then lets it become available. So - it's going to need the time to actually prep 100GB's worth of space. It also doesn't seem to completely shut things down, but more processing will be pretty seriously impacted.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

I also just came across this new little feature in 2005, which allows the file to grow WITHOUT zeroing everything out. It's got some security concerns, though, so you might care to think about it...

It's called Instant File Initialization - and is described in the BOL here:

http://msdn2.microsoft.com/en-us/library/ms175935.aspx

Also - the guy who seems to know a lot about it, (Paul Randal), posts here on a regular basis. You might get him to chime in a little more about it

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?