SQL server problem

Comments

• kriki Member, Moderator Posts: 9,118

  2006-08-28

  The .ldf is the transaction-log file. Every change made to the data of the DB is saved in that file as a sequence of actions.
  The best way to keep it small is to make regular transaction-backups (e.g. 1 backup each few hours and extra a full backup once a day).
  To decrease the size you first must make a full backup and then do a shrink of the transaction-log file.

  Regards,Alain Krikilion
  No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
  
  

  0
• DenSter Member Posts: 8,307

  2006-08-28

  The LDF file is your transaction log. It is so big because it has been logging your transactions and you probably never backed it up. Doing a full SQL Server database backup should purge the log up to and including the last active transaction.
  
  If you do not have a backup plan in place, and you do not intend to ever use the log for restoring your database in case of a catastrophe, you can set the recovery model to simple, and it will only log active transactions. Go to File, Database, Alter. On the Options tab select Simple as the recovery model. Do this while nobody is connected to the database, and make a backup of the database before you do it.
  
  If you DO intend to use the TL for restoring databases, then you will need to include a transaction log backup in your backup schedule. Read Books Online for more details as to how to do that.

  Daniel Rimmelzwaan
  RIS Plus, LLC

  0
• DenSter Member Posts: 8,307

  2006-08-28

  One way to vizualize the file and how much of it is used is to use the taskpad view. Click on the database in Enterprise Manager, then click view and select 'taskpad'. You should now see a visual representation of the database files. If you have a relatively large blue area (which is the unused free part of the file, then you can shrink it to decrease the size of the file. If most of the file is grey, which represents used space, then you need to back the log up before you can shrink the file.

  Daniel Rimmelzwaan
  RIS Plus, LLC

  0
• bbrown Member Posts: 3,268

  2006-08-29

  A few comments:
  
  Avoid shrinking the file too small. This can cause SQL to auto-grow the file which will impact system performance.
  
  Avoid constantly shrinking and expanding the log file. This will result in a file that is fragmented on the drive and hurt performance. The best approach is to reformat the drive (should be dedicated to log) and recreate the transaction log of the correct size. The correct size would be large enough to hold transaction between backups without auto-growing.
  
  Setting recovery method to simple (trunctate on checkpoint) does not disable the transaction log. A large transaction (i.e. Navision restore or table optimize) can stll cause the log to grow in size.

  There are no bugs - only undocumented features.

  0