locked
How to reduce ldf initial size? RRS feed

  • Question

  • Hi,

    My db recovery model is Full.

    ldf initial size is set to 60GB due to this i am facing disk space issue always.

    Is there any possible way to reduce initial size to 1GB.

    Friday, September 9, 2011 5:35 AM

Answers

  • Hi,

    My db recovery model is Full.

    ldf initial size is set to 60GB due to this i am facing disk space issue always.

    Is there any possible way to reduce initial size to 1GB.

    Note: The above method will shrink the log file. Shrinking is not good thing and recommended.

     If you have maintenance window detach the DB and rename the log file and attach it back with the help of "FOR ATTACH_REBUILD_LOG". Before doing this check the log file status with the help of dbcc loginfo().

     

     

     

     



    Muthukkumaran Kaliyamoorthy SQL DBA

    Helping SQL DBAs and Developers >>>SqlserverBlogForum

    Why would you ever do it this way? I certainly wouldn't, given that you can easily do this, as Abhay says, by using DBCC SHRINKFILE

    Just to add, if you shrink it down to its minimum possible size, try and pre-grow it to a reasonable size based on expected usage, and set an appropriate growth increment.  This will reduce the number of VLFs incurred in the future and help transactional throughput



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    • Edited by Andrew Bainbridge Friday, September 9, 2011 8:01 AM Added detail
    • Proposed as answer by Stephanie Lv Wednesday, September 14, 2011 12:37 PM
    • Marked as answer by Stephanie Lv Monday, September 19, 2011 11:32 AM
    Friday, September 9, 2011 7:57 AM

All replies

  • Enter The size You Want To Make It To And Press OK


    Mark as Answer If Reply Was Helpful Thanks Kuldeep Bisht Technical Lead @ Simplion Technologies http://mssqlguide.kuldeepbisht.com
    Friday, September 9, 2011 6:25 AM
  • Hi,

    My db recovery model is Full.

    ldf initial size is set to 60GB due to this i am facing disk space issue always.

    Is there any possible way to reduce initial size to 1GB.

    Note: The above method will shrink the log file. Shrinking is not good thing and recommended.

     If you have maintenance window detach the DB and rename the log file and attach it back with the help of "FOR ATTACH_REBUILD_LOG". Before doing this check the log file status with the help of dbcc loginfo().

     

     

     

     



    Muthukkumaran Kaliyamoorthy SQL DBA

    Helping SQL DBAs and Developers >>>SqlserverBlogForum
    Friday, September 9, 2011 7:13 AM
  • You can do this easily by running dbcc shrinkfile command .
    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Friday, September 9, 2011 7:41 AM
  • Hi,

    My db recovery model is Full.

    ldf initial size is set to 60GB due to this i am facing disk space issue always.

    Is there any possible way to reduce initial size to 1GB.

    Note: The above method will shrink the log file. Shrinking is not good thing and recommended.

     If you have maintenance window detach the DB and rename the log file and attach it back with the help of "FOR ATTACH_REBUILD_LOG". Before doing this check the log file status with the help of dbcc loginfo().

     

     

     

     



    Muthukkumaran Kaliyamoorthy SQL DBA

    Helping SQL DBAs and Developers >>>SqlserverBlogForum

    Why would you ever do it this way? I certainly wouldn't, given that you can easily do this, as Abhay says, by using DBCC SHRINKFILE

    Just to add, if you shrink it down to its minimum possible size, try and pre-grow it to a reasonable size based on expected usage, and set an appropriate growth increment.  This will reduce the number of VLFs incurred in the future and help transactional throughput



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    • Edited by Andrew Bainbridge Friday, September 9, 2011 8:01 AM Added detail
    • Proposed as answer by Stephanie Lv Wednesday, September 14, 2011 12:37 PM
    • Marked as answer by Stephanie Lv Monday, September 19, 2011 11:32 AM
    Friday, September 9, 2011 7:57 AM
  • Andrew agreed, Yes that’s easy method but

    Shrinking the 60GB production Db files will cause and increase the file fragmentation even OS too and took more I/O and CPU and even it will block the users.

    That’s why I mentioned "if you have maintenance window" then go for that method and make sure to put the db single user and shutdown it cleanly. Then create the new one and size it reasonably. Do the log backup regularly. 

    @Jilan See the survey  http://www.sqlskills.com/BLOGS/PAUL/post/Importance-of-proper-transaction-log-size-management.aspx



    Muthukkumaran Kaliyamoorthy SQL DBA

    Helping SQL DBAs and Developers >>>SqlserverBlogForum
    Friday, September 9, 2011 10:38 AM
  • The most practical method would be to decrease the log size. It is obviously important to note that the log may simply grow again, but the space gained in the short term may allow the op to make changes necessary to mitigate this problem in future.

    Shutting down SQL server in a maintenance window to use "FOR ATTACH_REBUILD_LOG" means that you have unnecessary down time, but as well as this by running that command you have also broken the log chain and will now need to do a full backup.


    Jon
    Friday, September 9, 2011 11:10 AM
  • Thanks Jon. I noticed the recovery model went simple after rebuild the log. So set the recovery model back to full and do the full backup.

     



    Muthukkumaran Kaliyamoorthy SQL DBA

    Helping SQL DBAs and Developers >>>SqlserverBlogForum
    Friday, September 9, 2011 11:30 AM