huge log file

  • Dear Experts

    I have a 3 GB mdf file and 355 GB ldf file.

    What should I do, I have taken a backup to the transaction log, should i delete the log file

    Thanks

  • zi (4/25/2013)


    Dear Experts

    should i delete the log file

    Thanks

    Please don't try this ever.

    Just try this.

    select log_reuse_wait_desc,* from sys.databases where name='dbname'

    -- see what it is waiting on

    also run this dbcc sqlperf(logspace)

    -- see log space utilization

    M&M

  • can i shrink the log file

    I want to solve the space problem

  • any idea how to solve this problem

    is it correct to make backup log then shrink the log file

    Thanks

  • What is the recovery model of your database? Is suspect it to be FULL.

    If your recovery model is FULL, follow the below steps

    1. Take a full database backup

    2. Shrink the log file to an appropriate size

    3. Take a full database backup again

    4. Schedule transaction log backups to avoid such issues in the future

    You can also check the below mentioned article for more information on Managing Transaction Logs

    http://www.sqlservercentral.com/articles/Administration/64582/


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • After you either backup your log or switch your database to simple recovery, you can shrink the log file. But you need to do one or the other. I have a blog post [/url]up the explains the problem and the best solutions.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • is it correct to convert the recovery model to simple then shrink the log file

    Thanks

  • zi (4/26/2013)


    is it correct to convert the recovery model to simple then shrink the log file

    Thanks

    Read the blog post that Grant provided you a link to above.

    Your t-log is growing because you are in full recovery model with, most likely, no regularly scheduled transaction log backups to maintain th size of the transaction log.

  • zi (4/26/2013)


    is it correct to convert the recovery model to simple then shrink the log file

    Thanks

    That question is best answered by the people who use your database. How much data can they afford to lose? How much down time can they have?

    As a quick fix converting to simple and then shrinking the log file (you may have to run a checkpoint or two before the log rolls over to the free space to allow shrinking) will work, but leaving the database in the simple model means no point in time recovery.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • (you may have to run a checkpoint or two before the log rolls over to the free space to allow shrinking) will work,

    how to run a checkpoint?

    Thanks

  • zi (4/26/2013)


    (you may have to run a checkpoint or two before the log rolls over to the free space to allow shrinking) will work,

    how to run a checkpoint?

    Thanks

    It's a t-sql command:

    CHECKPOINT

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • zi (4/26/2013)


    (you may have to run a checkpoint or two before the log rolls over to the free space to allow shrinking) will work,

    how to run a checkpoint?

    Thanks

    use [yourdb]

    GO

    checkpoint

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • zi (4/25/2013)


    Dear Experts

    I have a 3 GB mdf file and 355 GB ldf file.

    What should I do, I have taken a backup to the transaction log, should i delete the log file

    Thanks

    A sledgehammer approach is:

    1)backup database

    2)detach database

    3)copy mdf just in case

    4)rename ldf

    5)single file attach the mdf.

    End result:

    You have 5-10 minutes downtime.

    A log file that is small. Read the articles about what size it needs to be etc

    No waiting until next xmas for the logfile to shrink

    Suggest you practice this on a dev server as the permissions of the file can cause a slight hiccup

    Yes you can use the gui or a tsql script

    Just as an aside: how did it get into this condition? Many years ago I had a similar issue when some consultants came in took responsibility for a system and then no one bothered to tell us that the consultants had finished. 3 weeks later we had a 300GB log due to autogrow and no log backups. The term consultants at times is open to debate 🙂

  • Surely you mean 😉

    Steve JP (4/27/2013)


    1)switch to simple recovery

    2)backup database

    3)checkpoint database

    4)shrink log

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Depends as the logfile doesn't always want to shrink. If it doesn't then like I said a single file attach is a sledgehammer approach, never said it was a preferred method 😛

Viewing 15 posts - 1 through 15 (of 20 total)

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