Client Deletion, Transaction log getting full.

[Former Member]

Hi Gurus,

We are trying to delete a client by running:

clientremove

client = 200 (*200 being the client we want to remove)

select *

The transaction log disk space allocated is 50GB, it is getting full (in simple mode) and client deletion never completes. The size of the table it is accessing is 86 GB, and i think 200 client will be occupying around 40-45GB. Client 200 has 15.5 million rows in the table.

I am i giving proper command ?is there any explicit commit i can include or any workaround for deleting the client and not hammer the log file.

Thanks guys

Edited by: SAP_SQLDBA on Jan 22, 2010 6:51 PM

[ganesh_borase2]

Hi!

If I am not wrong, When you are trying to delete the client then due to the transaction log full, client deletion

process is not completed.

In this case, you can scheduled the log backup 15 min. interval so that log file never filled.

and you can continue with the client deletion.

Thanks

[Former Member]

Hi All,

I am bit new in SAP. I have few questios to ask. We are using db29.7 as database for SAP r/3 4.6C system.

My question are -

1) What is log space. Where do i find it out.

2) What is difference between log_dir full and log space full situation.

3) If log space is full, Which process should we terminate. How to determine which process is taking largest log space and which is holding log space from largest time period.

4) If i calculate size of llogspace like this

(primary log + secondary log)*size of log file

Is it right to calculate log space llike this.

5)What is the meaning of 1st active log in log_dir.

Please help with these questions.

Thanks

Sandeep

[former_member524429]

Hi,

Backup the active transaction log file and Shrink the file directly.

Please refer the following SAP Notes to get more information.

[ Note 625546 - Size of transaction log file is too big|https://websmp130.sap-ag.de/sap%28bD1lbiZjPTAwMQ==%29/bc/bsp/spn/sapnotes/index2.htm?numm=625546]

[ Note 421644 - SQL error 9002: The transaction log is full|https://websmp130.sap-ag.de/sap%28bD1lbiZjPTAwMQ==%29/bc/bsp/spn/sapnotes/index2.htm?numm=421644]

Regards,

Bhavik G. Shroff

[former_member524429]

Hi,

Backup the active transaction log file and Shrink the file directly.

Please refer the following SAP Notes to get more information.

[ Note 625546 - Size of transaction log file is too big|https://websmp130.sap-ag.de/sap%28bD1lbiZjPTAwMQ==%29/bc/bsp/spn/sapnotes/index2.htm?numm=625546]

[ Note 421644 - SQL error 9002: The transaction log is full|https://websmp130.sap-ag.de/sap%28bD1lbiZjPTAwMQ==%29/bc/bsp/spn/sapnotes/index2.htm?numm=421644]

Which version of SQL Server u are using ? SP Level ?

Frequently perform Transaction Log backup (BACKUP TRANS) to remove inactive space within the Transaction Log Files.

Please refer [Note 307911 - Transaction Log Filling Up in SQL Server 7.0|https://websmp130.sap-ag.de/sap%28bD1lbiZjPTAwMQ==%29/bc/bsp/spn/sapnotes/index2.htm?numm=307911] to get more information about the reasons for such kind of situation.

Regards,

Bhavik G. Shroff

[Former Member]

Thanks Bhavik for your reply.

I am constantly trying to back up the transaction log manually and shrinking the file directly. Its not working.

i use:

backup log 'logname' with truncate_only

result:

command executed successfully

then, shrink log file directly

dbcc shrinkfile ('logname', 2048)

result:

cannot shrink log file, the log space is minimum space required for the log file.

I monitor the log space usage during client deletion using DBCC SQLPERF(LOGSPACE);

I don't see any difference either in log size or usuage space when we manually backup log or shrink directly. The log file is growing very huge, last time it has grown to 50 GB.

Thanks guys.

[Former Member]

I know this is an old topic, and you probably resolved it by now, but since it is an unresolved question I thought I would chime in with an answer, so that anyone else searching for a the same solution will find it here.

I have found that sometimes if a transaction log fails to truncate it is due to a rogue process still running and basically locking the transaction log. If you identify the process and kill it, then the log can be truncated.

I have also found that when trying to release Log space back to the OS, the gui tools (SQL Management Studio) seem to work better for me.

Did you finally get your solution?

[markus_doehr2]

> I have found that sometimes if a transaction log fails to truncate it is due to a rogue process still running and basically locking the transaction log. If you identify the process and kill it, then the log can be truncated.

It doesn't need to be a "rogue process" but a long running transaction - as is a client deletion. Of course, if you kill the R3trans you can shrink the log.

> I have also found that when trying to release Log space back to the OS, the gui tools (SQL Management Studio) seem to work better for me.

They all do the same.

If you do a client deletion using R3trans as described, the system will do something like "delete from mara where mandt = 001". The engine need to access all the rows and delete them. To ensure consistency in case of a crash of the server, all the information is put in the transaction log. So this log needs to be as big as the to-be-deleted content of the biggest table.

If you delete a client on ABAP level you can choose to use a parallelism and entries are deleted in blocks and not as a whole so you may need less space.

You can also switch the database logging to "bulk" and, if the log is still growing too much, set a trace flag (-T610) so even less log is created.

Markus

[Former Member]

>

> If you delete a client on ABAP level you can choose to use a parallelism and entries are deleted in blocks and not as a whole so you may need less space.

Can you please explain it in a bit detail, or point to any other source. i am a kind of newbie basis admin.

Thank you Markus for the reply.

Joey,Yes, we did solved the problem by increasing the T-log file space. thank you for your suggestion.

[markus_doehr2]

> > If you delete a client on ABAP level you can choose to use a parallelism and entries are deleted in blocks and not as a whole so you may need less space.

>

>

Check

http://help.sap.com/saphelp_nw70ehp1/helpdata/en/69/c24c9c4ba111d189750000e8322d00/frameset.htm

Markus

[former_member627490]

Hi ,

We have the same issue with Transaction log full. Are you guys fixed this ? Below is my issue:

We have weekly and monthly jobs running in our BW Production system with huge back up transaction logs...Due to these loads we are getting CCMS alerts of t log space issues. When we checked in DB02 the Recovery mode is set to FULL...with log space as 60 GB limit.....So our DB team has changed the setting to SIMPLE and it is not giving any issues .

I requested them to change to Simple always but they said Transaction log back up should be FULL always. Can any one let me know what happens if we change this setting to Simple for ever instead of just changing it on weekly and monthly runs?

We have the weekly and monthly BW jobs for Cube compression and PSA deletion and I never faced this issue before when I worked with other places...IPlease advice

[Former Member]

Hi,

please have a look at SAP Note [1297986|https://service.sap.com/sap/support/notes/1297986]. It explains why the Recovery Model FULL is the right choice.

Regards,

Sven