Post reply

Resizing Transaction Log

  • September 20, 2006 at 3:29 am

    #170428

    Hi All,

     

    Hi all, I have a transaction log that has been sized to 100GB, If I wanted to resize this, the way I know how to do it is to detach the database and put make up a new transaction log.

     

    Does anyone know if there is a better/faster way of doing this ?

     

    Thanks in advance.

    Kindest Regards,

    John Burchel (Trainee Developer)

  • September 20, 2006 at 4:36 am

    #661442

    Firstly back up the transaction log.

    Next run sp_helpdb 'YourDatabaseName'

    Note the name of the log device

    now run DBCC SHRINKFILE('TheLogFileName','Your desired size')

    LinkedIn Profile

  • September 20, 2006 at 5:09 am

    #661445

    Thanks for the reply, this method will simply shrink the log, I am looking for a way to actually reduce the initial size the log file has been set to.

     

    Thanks

     

    Kindest Regards,

    John Burchel (Trainee Developer)

  • September 20, 2006 at 12:48 pm

    #661574

    After shrinking the log look up ALTER DATABASE MODFIY FILE in Books on Line.

    LinkedIn Profile

  • September 20, 2006 at 3:35 pm

    #661589

     

    before shrink put the DB in simple mode, full backup, run shrink, then set to full (restore mode).

    This should help you to set the size to original one

     

  • September 20, 2006 at 4:43 pm

    #661596

    FYI: My databases are in Simple mode. This will only work if you only have one log file.

    I detach the database, rename the log file to *.ldfold and then attach the database. A new log file is created based on the size of the Model DB's log. Then you can alter the size to what you need.

    At that point, you can delete the *.ldfold.

    HTH,



    Michelle

  • May 24, 2007 at 5:53 am

    #708589

    Hi all,

    we had similiar issue. Thanks to NETs solution above we are able to resize the transaction log to reasonble sizes.

    My question is if this can be done via script someway.

    thx

  • May 24, 2007 at 6:49 am

    #708607

    Yes,

     

    detaching and attaching is another solution

     

    Thank

Viewing 8 posts - 1 through 7 (of 7 total)

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