Transactional log grows abnormally to 160GB

[Former Member]

Dear All

I am new to SQL database . Im my production server transactional log grow upto 160GB and only 7 GB left in the file system

Like dont want to add the any space in the file system and we have everyday full backup schedule.

Is there any permanent solution. I want to take the proactive action before my database hang.

Is also check the note 421644.

[Former Member · Accepted solution]

Hi

For proactive monitoring i usually use a counter 'Percent log used' which will alert you if the logs fills above the configured threshold level (such as >90%).

If you had a long transaction, your log file may grow huge and stays the same size ,and If you are backing up log frequently you will see lot of free space inside the log space. The database hangs if the log file is full and you are out of disk space on log drive.

you may also add extra disk space, to avoid out of disk space situation if log grow again.

Thanks

Mushtaq

[Former Member]

Thanks Experts

I have followed the note to shrink the transactional logs and revised the backup plolicy as suggested.

[Former Member]

Hi Tarik!

Probably the fact that your TLOG is so high is because you did not back it up at all in the beginning or you just did not back it up for some time. This does not mean that its allocated space is full, and in fact, if you defined a proper backup policy you can shrink it to a more reasonable value as explained in SAP note 363018 (section E.b ).

Now, in order to avoid having it grown so much again is to ensure that you perform TLOG backups regularly (the frequency depends on your business needs, you should take your own conclusions depending on your system activity); check the autogrow settings just in case a single grow means a huge increase (imagine you set 120 GB!).

In case that you have any problems still e.g. because the TLOG grows even though you back it up regularly, you can check if there is a open transaction that prevents it to be backed up, as you already know note 421644.

Good luck!

-Jesú

[Former Member]

You should configure transaction log backup in regular basis to avoid your SQL Log being oversized,

Also, please check SQL Recovering mode according to note Note 421644 - SQL error 9002: The transaction log is full

You should always set your database it to "FULL-Recovery" according SAP Best-Practices, but if you will do any system changes in your system that has many INSERT-UPDATE-DELETE operations, lilke applying support packages or DB REINDEX, you shoud have to temporary change the recovery mode.

To reconfigure the size of transaction LOG, follow the instructions of SAP Note 625546 - Size of transaction log file is too big

Please always do a full backup of your database before doing any modifications on transaction log.

Hope that it helps you

Kind regards,

Marcelo Macedo

[xymanuel]

Hi Tariq,

how often do you backup your log during the day?

Schedule a logbackup every 15min or so, to avoid overflow of your logfile.

Kind regards

Manuel