SQL transaction log unexpected growth

Comments

• Options

  Iqbal_Febriano Member Posts: 66

  2005-04-28

  Go to SQL Server Enterprise Manager, choose the database, right click on it, select all task -> shrink. That shall reduce your log file's size.
  If your client has lot of daily transaction, it's normal to have such a huge log file. This shrinking task should performed periodically, in order to maintain the log file in its defaultl size

  Be fast, be straight, be quiet

  0
• Options

  ningnongjin Member Posts: 42

  2005-04-28

  Hi Iqbal,
  
  Thanks for the feedback. We actually tried the SQL Analyser to truncate it, but it did not clear the size. They are still able to perform their daily activities, so what we're looking at is that the size 100GB+ is specially allocated to Navision regardless if they utilise it or not.

  0
• Options

  pindev Member Posts: 22

  2005-04-28

  You have to truncate the log file first and after that you will be able to shrink the physical file.
  So the first command is
  backup log <database name> with truncate_only
  and then
  dbcc shrinkfile (<log_file_name>, <desired size in MB>)
  
  Regards.

  0
• Options

  Feldballe Member Posts: 26

  2005-04-28

  Before doing any shrinking you should test your backup!
  If you perform a correct database backup and transaction log backup using EM, the transaction log should reduce itself.
  
  Performing a backup routine with a combination of database backup (night time) and periodically transaction log backups during workhours, should resolve your problem.
  Just make sure both database backup-files and transaction log backup-files are secured using your third party program.
  
  Backing up databases all depend on how much time you or your client are willing to loose, when the system breaks down...

  0
• Options

  ovicash Member Posts: 141

  2005-05-04

  ningnongjin wrote:

  Hi All,
  
  I'm seeking some advice here regarding the SQL transaction log. I have a client whose transaction log grew to 100GB plus in a span less than 6 mths. Backup was made daily using a third party tool. The database file and transaction log file in Navision was left to its default and no restriction was place on its growth with an increase in 10%. How did this situation arised?Could the backup performed was incorrect. Thanks in advance.

  Be careful, make sure that you are in single user mode, or nobody else is using the database. Otherwiseit will not shrink.

  ovidiu
  
  Best Regards

  0