SQL transaction log unexpected growth

ningnongjin
ningnongjin Member Posts: 42
edited 2005-05-04 in NAV/Navision Classic Client
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.

Comments

  • Iqbal_Febriano
    Iqbal_Febriano Member Posts: 66
    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
  • ningnongjin
    ningnongjin Member Posts: 42
    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.
  • pindev
    pindev Member Posts: 22
    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.
  • Feldballe
    Feldballe Member Posts: 26
    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...
  • ovicash
    ovicash Member Posts: 141
    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

Categories