ldf file which is very large.

Posted on 2009-12-18
Last Modified: 2012-05-08
I have a large .ldf file and would like to delete that file and create a smaller .ldf file.
Question by:vera2006
    LVL 75

    Accepted Solution

    change the recovery model to simple and after running the following command change it back to the original

    DBCC SHRINKFILE (YourDB.ldf , 10 )
    LVL 30

    Assisted Solution

    by:Reza Rad
    don't delete this file
    just Shrink your database,
    right click on database in enterprise manager and then choose shrink in tasks menu

    you can also set recovery mode of your database to simple , this will keep your log file (.ldf) small

    LVL 26

    Assisted Solution

    what is servers recovery model setting? it should be full recovery. In this case you should do proper backup operations to truncate the ldf file.
    if you want to truncate the ldf file without doing any backup then you should use the commands

    USE DatabaseName
    DBCC SHRINKFILE(<TransactionLogName>, 1)
    DBCC SHRINKFILE(<TransactionLogName>, 1)
    LVL 41

    Assisted Solution

    There is an art (and some science) behind managing log file sizes...   I recommend that you take a few minutes to look at the following article
    The bottom line is this....   you control the growth of the log file by the frequency of periodic log backups, and you control the size of the log file with a shrink operation
    LVL 4

    Assisted Solution

    First thing you need to check is why is that transaction file so huge. If the transaction file is growing everday so much then there is no point in shrinking the file as it will again grow.

    before you truncate the log please check the below things
    1) check if there are any open transaction of the database which is associated to that log file

    use <dbname>
    dbcc opentran

    If it says no open transaction then execute the below query will give you how much the log is used for all the database

    dbcc sqlperf(logspace)

    the results are in percentage

    if the log file is grown only few percentage then directly go for shrinking option
    dbcc shrinkfile('logfilename',<size in MB>)

    if the database is present and you want delete the data present in the log file then go below query

    If the file has grown only once and now you want to shrink it then go for

    use master
    backup log <databasename> with truncate_only

    this will clear the log file , and then again go for shrinking option.
    LVL 13

    Assisted Solution

    The comments above that describe fixing the underlying problem through analysis and thought are dead on. the ones that simply suggest you shrink your log file are not.

    The real answer is to right size your transaction log from the beginning to handle potential future size. Then decide which recvoery model is right for your company. Full Recovery model means you want to restore to a specific point in time and minimize data loss in the event of a failure. You have told SQL Server to allow the log file to continue to grow and grow until you manually backup your log. This is because you have told SQL you want to take care of the restores at the log file level and use that log file to recover with after you back it up.

    In Simple recovery model you are telling SQL that the last point you ever wish to restore to is your last full or differential backup. You are giving SQL permission to truncate the log file all by itself at certain internal intervals in SQL Server which allows the log file to be reused. This keeps the size down to a minimum.

    Once you figure out your recovery needs choose the model that works. If Simple recovery model is right for your SLAs and business needs then you should just be able to set that and leave it alone. Perhaps consider shrinking the log file down to it's smallest size and then grow it carefully to the size that allows for future growth (look at this blog post about why you should care about regrowing in chunks to get the right number of VLFs:

    I have a series on my blog that talks about the perils of shrinking and just truncating the transaction log and has some info on why the log can grow and management strategies.  That series is here:

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    755 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

    21 Experts available now in Live!

    Get 1:1 Help Now