cancel
Showing results for 
Search instead for 
Did you mean: 

Faster process for SAP Client delete

Former Member
0 Kudos


Hi ,

I have just completed a homogeneous system copy (MS SQL Server-Specific: Detach/Attach or Backup/Restore) process.

Currently, I am doing a post processing steps. I have a copy a master client from the source system client (800) to the current system client (111).

1) I would like to know if there is any faster process for SAP full client deletion. I have been deleting the client since past 2 day and it's still 60 % completed. We have a space issue so we have to delete client first then copy the master client into another client.

2) I would like to know if I have to run BDLS. I think I don't have to since I used backup/restore process. How can I verify at db level that all the tables have the current SID ?

Our database is MSSQL 900GB.

Thanks

Mohammad Farooq

Accepted Solutions (0)

Answers (4)

Answers (4)

former_member206857
Active Participant
0 Kudos

I've personally been playing with Parallelism in SQL 2012. I wonder if a client delete would benefit by the MaxDOP setting?

I don't think you are at SQL2012? SAP only supports it in 12

  • It applies only for SQL Server 2012 or higher
  • For SAP BW System there is no change, means the old recommendation of 1 is still valid. See note 1654613 for details.
  • If the server has less than 16 CPUs the old recommendation of MaxDOP = 1 is still valid
  • If the system has 16 to 63 CPUs you might set it to 1 or 2
  • If there are 64 or more CPUs available, valid settings are 1, 2, 3, or 4
  • A value of zero (0) is not allowed at any time (this setting means the SQL Server can decide how many threads it is using for the query (from 1 up to #CPUs, but not necessarily all CPUs))
Reagan
Advisor
Advisor
0 Kudos

Using R3trans is not going to be very quick to delete a big client. I have tried that and the process takes almost the same time even if you do it from SAP. Have you thought of truncating the client specific tables from the DB level ? If you truncate the tables that are only used by the client (to be deleted) then you can achieve this in a minimal time. List all the big tables specific to the client and truncate them. Start the client deletion from SAP.

Former Member
0 Kudos

Thank you all.

Benjamin, to truncate the tables I thought about it but am afraid I delete tables that is being used for other client. I have multiple clients and would like to delete only one client due to space issue.

Plus I don't know how to truncate the table in MSSQL or windows 2008. I need to know the commands. if you have any snapshot that will be helpful

Thanks

Reagan
Advisor
Advisor
0 Kudos

Again, like I said you only need to truncate the tables ONLY used by the client to be delete.

I am quite sure there is truncate in MSSQL as well. You may do a quick google search for MSSQL truncate table. My experience is based on Oracle (above suggested). I am sure you can reduce the run time of the client deletion with the above approach.

Cheers

Reagan

Former Member
0 Kudos

Thanks Benjamin, how would I know the client specific tables so I delete tables from that client only.

former_member185239
Active Contributor
0 Kudos

Hi Farooq,

Follow the sapnote 857973 - Deleting clients efficiently using Oracle

Though it is relevant to oracle it might provide some light.

With Regards

Ashutosh Chaturvedi

Matt_Fraser
Active Contributor
0 Kudos

Are there tables that are truly only in specific clients at the DB level? I think the only thing that distinguishes one client from another at the table level is the MANDT field. So, I don't think you'd be able to truncate the table entirely, or you would lose the data from other clients in it, unless you can be certain that there are no rows in the table for any other client. That could be exhaustive to determine, although the effort may be worthwhile for the top x largest tables.

Mohammad, another possibility, instead of using TRUNCATE TABLE at the SQL Server level, would be use DELETE <table> WHERE MANDT = <client to be deleted>. This would not be nearly as fast as a TRUNCATE, but you might consider putting the database in SIMPLE recovery mode, then using the DELETE, table by table for your largest tables, then returning the database to FULL recovery mode and taking a new full backup.

I'm not sure I would actually recommend this process, but as this is a test system, you can certainly give it a try. The worst-case scenario is that you have to restore it again from your original source copy.

Is there a reason you can't just use the same client number in the target system as in the source system?

Regards,

Matt

Reagan
Advisor
Advisor
0 Kudos

Hello Matt,

In my case I had to refresh a development system with the production system backup having a database size of 3 TB. The development system client was not the same as the production system so I had to create a new client on the development system and then trigger a local client copy using the profile SAP_UCSV and it was successful. The production client was not required and for that we triggered a deletion but it was running for days. I terminated the deletion and truncated the tables (except the cluster tables) that were used ONLY by the client to be deleted. I was basically checking the tables from Tx SE16 on the new client and if not present the table was truncated. This worked for me. Probably not the best solution but had to do it due to time constraints. There is an article available for Oracle / Unix based systems

Regards

RB

Matt_Fraser
Active Contributor
0 Kudos

That's a pretty good process that Daniel Lippman lays out, and it should be easily translatable to SQL Server.

daniel_lippmann5
Active Participant
0 Kudos

This document has moved, so here is the new location:

Former Member
0 Kudos

Hi Mohammad,

You can try below steps:


1.Create a small
command file for R3trans, as follows:

# Client
deletion using R3trans - command file

# ------------------------------------------------------

Clientremove

client = <client #>

select *


2. Name this command
file as "client_deletion"

3. Copy the command file to the directory /usr/sap/trans/bin

4. Now call R3trans with the following parameters:

  R3trans
-w <log file name> -u 1 client_deletion

The parameter w signifies to write the output to a
named log file explicitly. The default log file is 'trans.log'.

The parameter u signifies the unconditional mode (i.e. forces R3trans to do
things which usually are rejected).



5. You can monitor the client deletion my tailing the log file on a UNIX machine.

tail -f <log file
name>

Br Vaibhav

Former Member
0 Kudos

Thanks Vaibhav,

We are running windows 2008 MSSQL is this applicable to windows or only for Unix.

divyanshu_srivastava3
Active Contributor
0 Kudos

The other process which I think is fast would be a direct delete from OS level, as logging par at application level is missed.

You may refer -

BDLS should be run and is intrinsic after refresh and copies.

Regards,