SQL transaction log unexpected growth

ningnongjin

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.

Iqbal_Febriano

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

ningnongjin

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

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

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

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.