Transaction Log File

[Former Member]

Hi,

We have a SAP instance in production where the transaction Log file is taking a lot of disk space.

When we are going on the DB02 transaction => database option we have the recovery_model witch is set to FULL.

We have try to SHRINK the log file but we have this message like discribe in SAP note 363018:

The backup is done with an external tool ( NETVAULT) with a database shutdown every night.

Cannot shrink log file 2 (PE1LOG1) because all logical log files are in use.

What is the solution for decreasing this log file ?

Regards

Edited by: Hazard Bruno on Apr 29, 2010 9:27 AM

[Nibu]

Hi Bruno,

Try if you can take the Transaction log backup to any external drive . Once you take the transaction log backup, the log will be reduced. Schedule Tlog backup often , as per SAP best practice, you should schedule Tlog backup in every 30 mins for Production systems . If you can can Tlog backup atleast once in a week, such a situation will not occur.

Regards,

Nibu Antony

[Former Member]

Hi

As Nibu mentioned, taking frequent transaction log backups will bring down the size of your transaction logs. Moreover you may re-architect your backup plan, for a full backup you have to shutdown your database ?? you can go for online backup and there are third party tools like quest litespeed for compression (SQL 2008 has backup compression).

my 2 cents

Thanks

[Former Member]

> If you can can Tlog backup atleast once in a week, such a situation will not occur.

Well, on our production systems we backup the Tlog every 3 hours and when the load is high, this is not enough and we have to add more backups.

Regards,

Olivier

[Former Member]

Well, for high load production systems the transaction log backups should be in shorter intervals like 15-30 minutes.

What is your %log filled size, right after tlog backup?

you can find %log full by running 'dbcc sqlperf(logspace)' in management studio.

[Former Member]

Let me add a few thoughts to this.

The recovery model FULL is not set for the sake of it! We want the transaction log to be written for being able to restore the database to a certain point of time in case of hardware crashes or unwanted user actions (e.g. drop table). So the frequency of transaction log backups is mainly driven by the amount of data that your company can afford to loose from a business perspective. And that is usually not more than 30 or 60 minutes on OLTP systems.

Sven

[Former Member]

Thx for your help.

I have read the post but i don't understand what do this command :

BACKUP LOG DatabaseName WITH NO_LOG

Other information i have read in google that this command don't work in MS SQL SERVEUR 2005.

http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/7608a3cc-3beb-4d71-aa55-cbf...

Other problem the space left on our disk is to : 25GB and our database take 90GB and our transaction log is: 70GB

What do you think to put the recovery_model to SIMPLE and SRHINK the Log and after put the recovery_model to FULL? Could it be a valid method?

Regards.

[Former Member]

Hi,

Yes

Try changing DB to simple mode (but this will break your tlog target DB, which means you have to re-establish your log shipping target DB all over again). Once DB is in simple mode, cycle the SQL engine, then perform the truncate . This should bring your LOG DB down in size.

Once LOG DB is small again, change DB to full mode again and configure tlog shipping.

regards,

kaushal

[Former Member]

Thx for your help Kaushal Malavia.

We will test the operation but i don't know how to configure tlog shipping!!!

[Former Member]

Hi,

Log shipping means if you want to change location of your tlog file. it's just suggestion if you facing short of space on your server.

regards,

kaushal

[Former Member]

Hi,

check might helps you.

regards,

kaushal