Post reply

Best way to change initial log size of a live db?

  • February 8, 2010 at 11:47 am

    #138820

    Hello,

    We have a DB who's log-size is set to 16GB as the "initial size", the log is 99% free space, but I cannot release this space as the initial size is set so high.

    Ultimately, I want to change this to about 100MB as there is no reason for it to be this large.

    What is the best approach to do this?

    I tried to just change the Initial Size from the Properties window, but this does not work.

    Any help appreciated.

    Thank you.

  • February 8, 2010 at 12:01 pm

    #1115714

    You could shrink the log file first and then set it to what you want.

  • February 8, 2010 at 12:39 pm

    #1115742

    use dbcc shrinkfile to shrink the logfile.

    Warning:

    Many times the logfile is the size it is for a reason...

    whether it's logging reindexes or what have you.

    If you're really concerned about the size of the log file and you understand the ramification of changing your database's recovery model, you might consider changing it to one of the 2 types that are geared toward minimizing the log file size.

    Be careful, though. You'll limit your ability to perform a point-in-timer recovery if you change the recovery model to simple or bulk-logged. That's OK in some cases but be SURE this is what you want.

    ~Craig

    Craig Outcalt


    Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
    My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

  • February 8, 2010 at 1:32 pm

    #1115791

    Have you check how full your log file is after rebuilding indexes?



    My blog

Viewing 4 posts - 1 through 3 (of 3 total)

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