Post reply

Changing value of "Initial Size (MB)"

  • 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

  • December 5, 2007 at 1:44 pm

    #757432

    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?

  • December 5, 2007 at 2:36 pm

    #757447

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

    Thanks,

    carlos

  • December 5, 2007 at 2:46 pm

    #757450

    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.

  • December 5, 2007 at 3:10 pm

    #757456

    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?

  • December 5, 2007 at 3:14 pm

    #757457

    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?

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

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