Post reply

Changing the Recovery Model and Reducing the T-Log Size

  • October 22, 2004 at 2:42 pm

    #102248

    I have inherited a Database which is set as Full Recovery Model and has a T-Log with a size of 18 GBs. I have since found out that we do not have to keep the T-Logs for this Database. What may I do to reduce the size of the T-Log?

    Should I:

    1) Run the 'Backup Log DBName With Truncate_Only' Command. Would this reduce the size of the T-Log back to it's original size? (I did a Shrink Database Operation but it did not reduce the size of this T-Log.)

    OR

    2) Change the Recovery Model from Full to Simple. May I do this while the Users are online? What affect would changing the Recovery Mode have?

    Thanks in advance for any ideas or suggestions.

     

     

  • October 22, 2004 at 3:40 pm

    #527725

    Yes to both.

    Try running this command to shrink the log:

    DBCC SHRINKFILE (<log_file_name>, 1000) 

    Please note that the log_file_name is the logical name, not the physical location of the file.

    To keep the log file from growing in the future, set the recovery model to simple.  You can do this during production hours.  Basically, this will remove transactions from the log after they are commited and written to disk in the database.

     

    Hope this helps,

    Kathi

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

Viewing 2 posts - 1 through 1 (of 1 total)

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