Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How to reduce LDF file size in SQL 2000. LDF file growing too large.

Answered How to reduce LDF file size in SQL 2000. LDF file growing too large.

  • Friday, July 27, 2012 3:51 PM
     
     
    Sign In to Vote
    0
    I have a SQL 2000 server (I know it is old) that is hosting a few databases.  I recently discoverd that the LDF file for one of my databases is 28Gb while the database itself is only 8Mb.  I am looking at other posts regarding this same issue but I am only finding information on later versions of SQL server like 2005 and 2008.  Can someone help me figure out what I need to do to reduce the size of this log file?  I have been reading about Full Recovery mode and Simple recovery mode.  But again, this is SQL 2000 and some of the options are different.  thanks
     

All Replies

  • Friday, July 27, 2012 4:04 PM
     
     Answered
    Sign In to Vote
    0

    Hi Poly,

    If your database is in Full recovery mode then to clear the log file, you will need to backup transaction log and then shrink back the file to a appropriate size.

    If you dont want point-in-time recovery then you place the Database in simple recovery mode to clear all the inactive log. You will to shrink the log file to a appropriate size and make a good initial size of log file to prevent autogrows which might hurt performance later.

    If the above does not work then check if database is in replication as publisher which is preventing the log file to clear. If this is the case then you will need to troubleshoot the replication as why the transactions are not being sent to distributer. Check if log reader agent is working and SQL Agent is turned on.

    Please let me know how you get on.....

    Vamshi SQL DBA(MCITP) My Blogs: http://sqlserver-dba.co.uk http://sql-developers.co.uk/


    • Edited by Vamshi.rb Friday, July 27, 2012 4:05 PM
    • Marked As Answer by Poly Admin Monday, July 30, 2012 4:19 PM
    •  
     
  • Friday, July 27, 2012 4:09 PM
     
     
    Sign In to Vote
    0

    In SQL Server 2000 you can take backup with truncate log option (confused check google)

    if you are not using log Shipping you can change the recovery mode and from Full to Simple and then shrink the database the log file,

    check my blog for best practice to shrink log file

    http://sqlservr.blog.com/2012/06/26/best-practice-to-shrink-in-logldf-file-in-sql-server/

    Ramesh Babu Vavilla MCTS,MSBI

     
  • Friday, July 27, 2012 4:25 PM
     
     
    Sign In to Vote
    0

    I just checked and my db is in Full recovery mode but I have not been taking backups of the log files.  I need to put it in Simple recovery mode.  I have backed up the log file and have gone thru the process of shrinking the log file but it still is very large > 26Gb.  While trying to shrink I have tried both of the first two options, #1, "Compress pages and then truncate free space from the file", and #2, "Truncate free space from the end of the file", but I have not selected the third option which is "Shrink file to:" 

    The current size (of the log file) is 25468Mb and the space used is 354Mb, so I guess I could use the "Shrink file to:" option and specify maybe 400 or 500 Mb. 

    Thoughts? 

     
  • Friday, July 27, 2012 4:26 PM
     
     
    Sign In to Vote
    0
    please see above post.  thanks
     
  • Friday, July 27, 2012 4:34 PM
     
     
    Sign In to Vote
    0
    If you think 500MB is enough, just go with Shrink file to 500MB.

    Vamshi SQL DBA(MCITP) My Blogs: http://sqlserver-dba.co.uk http://sql-developers.co.uk/

     
  • Friday, July 27, 2012 4:40 PM
     
     
    Sign In to Vote
    0
    If I set it to Simple recovery mode and shrink the log file to 500Mb it can still grow if needed correct?  It is set to "Unrestricted file growth".
     
  • Friday, July 27, 2012 4:44 PM
     
     
    Sign In to Vote
    0

    It is always recomended to pre size your log file to avoid auto growths during business hours which would mean the users will experience pauses when the autogrowth occurs due to the Zero initialisation of Log File.

    The autogrowth also caused the physical file fragementation on the Physical Drive which should be avoided.

    Hope this helps...

    Vamshi SQL DBA(MCITP) My Blogs: http://sqlserver-dba.co.uk http://sql-developers.co.uk/

     
  • Friday, July 27, 2012 4:56 PM
     
     Answered
    Sign In to Vote
    0

    Ok, issue resolved.  After I changed the Recovery model from Full to Simple, then shrank the log file, this time it decreased to 1Mb.  I then presized it to 100Mb.

    So I guess the answer for this situation is first determine what recovery model you want.  Then after that if you choose Full you must backup the logs regularly.  Also if they grow too big due to not being backed up, then back them up, then shrink the database log file by choosing the "Shrink file to:" option.

    Does this sound correct?  thanks for your help.


    • Edited by Poly Admin Friday, July 27, 2012 4:58 PM
    • Marked As Answer by Poly Admin Monday, July 30, 2012 4:19 PM
    •  
     
  • Saturday, July 28, 2012 8:59 AM
    Moderator
     
     
    Sign In to Vote
    0
    Yes, that about sums it up.

    Tibor Karaszi, SQL Server MVP | web | blog

     
  • Monday, July 30, 2012 9:03 AM
     
     
    Sign In to Vote
    0

    Can you please mark the Thread as Answered and tick the correct answer?

    Thank you...

    Vamshi SQL DBA(MCITP) My Blogs: http://sqlserver-dba.co.uk http://sql-developers.co.uk/