Changing DB from Full to Simple Recovery - and how to truncate the current transaction log to free space

Posted on 2011-10-16
Last Modified: 2012-05-12
Can someone please help or point to instructions on how to #1 truncate the current transaction log which is too large and #2 change the database to SIMPLE recovery from FULL.  (should the current log be truncated first and then the database recovery changed, or will the process of change the recovery mode truncate the log.  Also, will truncate automatically reduce the log size, or will it need  a 'shrink' process to get it back to a acceptable size?)  If multiple steps, please direct me to the correct order - much appreciated.
Question by:BigSkyCountry
    LVL 23

    Expert Comment

    by:Rajkumar Gs
    These are the steps, to shrink database.

    // Shrink SQL Server database log file
    1. Execute this command against the database
    Dump transaction <<your db name>> with no_log

    2. Right-Click database - Shrink Files - Select 'Reorganize pages before releasing usused space' - Enter 'Shrink File to ' - 0 - Click OK

    LVL 23

    Expert Comment

    by:Rajkumar Gs
    Recommend to backup your database before attempting this
    LVL 23

    Expert Comment

    by:Rajkumar Gs
    Changing Recovery Model
     Recovery Model - Settings
    LVL 75

    Accepted Solution

    1.  Change the Recovery Model to Simple.

    2. Execute several checkpoints in a row:

    3. Shrink the database to an appropriate size:
    DBCC SHRINKFILE('YourTransactionLogFileNameGoesHere', 2000)     -- 2GB.


    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now