Query on BALDAT and BALHDR Table Size

[ketxorange7]

Hello experts,

I need your help to enlighten me if there is any way to know the specific table size in BALDAT and BALHDR table. I am currently doing an archiving via sbal_delete to perform the deletion for old logs in the system. I am instructed to perform the deletion on a yearly basis, where I have to record the before and after deletion size of every year, which I monitor through DB02 transaction, recorded before and after running sbal_delete.

However, as I am performing the deletion, the size of both table keeps expanding as the new logs comes in every minutes and because of that, I can't get the exact space size of the deleted logs on that particular year I am deleting.

Therefore, I would like to know, is there any way where I can get the exact size of these two tables, filtered by yearly records, or any efficient way where I can get the size on before and after deletion more accurately?

Regards,

Shakir

[Former Member · Accepted solution]

Hi Sharik,

Write report will give you the space savings, in the spool will get the actual results how much it deleted from both tables.

Ex:

If you want to analyse on it before running the write job. You can only estimate, you can not get the actual size. By considering the total table size(sum of BALDAT+BALHDR table sizes) / no. of entries in BALHDR table. By doing this you can get the size of individual entry. Based on this you can calculate the size of tables and can estimate the savings on each year.

Actual savings in the tables can get only after table re organization, you would not get the benefit immediately.

Regards,

Sudheer T 

[ketxorange7 · Accepted solution]

Hi Sudheer T,

Thank you for your reply. Can you show me how to get to the screenshot that you attached please? Which transaction is that?

Regards,

Shakir

[Former Member · Accepted solution]

Screen shot is from spool of the write job... just select the write job and click on spool and select the spool. In the write job spool you can get the Summary details for the entire write job.

Regards,

Sudheer

[ketxorange7 · Accepted solution]

Hi Sudheer,

By considering the total table size(sum of BALDAT+BALHDR table sizes) / no. of entries in BALHDR table, does this means that one entry in the table hold the same size across the table?

Eg,

Total Size: 10 MB

Total Entries: 100 entries

Size per entries = 10 MB/100 entries = 0.1 MB

Therefore,

Entry 1 = 0.1 MB

Entry 2 = 0.1 MB

.

.

.

Entry 100 = 0.1 MB

Is the size per entry are always the same in SAP table?

[Former Member · Accepted solution]

Hi Shakir,

Yes, this way you can get the approximate savings by just estimating.

We dont have any standard reports/formulas in SAP Archiving to estimate the savings on each entry basis.

Regards,

Sudheer T

[ketxorange7 · Accepted solution]

I see. Thanks for the helpful input, Sudheer.

Regards,

Shakir

[Former Member · Accepted solution]

Hi Shakir,

Please close the thread.. if your issue resolved.

Regards,

Sudheer T

[Former Member]

Use this program DA_STAT_ANALYSIS enter your archiving object and get all the details you want.

Cheers..!!!

[ketxorange7]

Hi Manjunath,

Thank you for your input. I believe that program is for the archived items. It's a different case for me as I am using sbal_delete, not archiving the logs using sara etc.

Regards,

Shakir

[Former Member]

Hi,

The first table doesn't have date field in there but the second table has the date field.

So you can use the ALDATE fields for the second table in the query.

You can go in se16 and check the no of entries using the ALDATE field.

Why dont you see the size changing. I think this will explain you more.

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/a0e8dfdc-2a92-4352-b5ca-7f52f7511299/tables-...

You can still see the size of the table and the extent in DB02 -> space -> segments -> details analysis and then give table name.

Thanks

Rishi abrol

[ketxorange7]

Hi Rishi Abrol,

Thank you for your reply. However, it is not the number of entries that I am looking for, but it is the size, as in MB, GB etc.

As far as I know, sbal_delete program will delete an entries from both BALDAT and BALHDR, as both these tables are related where BALHDR contains the header data and BALDAT contains the application logs data.

I understand about the size changing that you mentioned. The way I calculate the size difference now is, I took a screenshot of total space size before deletion in DB02 transaction and took the screenshot again after the deletion, and minus it to see the differences. This, however, will not give an accurate results as the table size keeps increasing as we speak.

I know that the column LOG_HANDLE is the one that match the entries on BALDAT and BALHDR, and I was thinking, is it possible to use mysql query to join these two tables and return the total size consume by yearly log entries? Any idea?

[Former Member]

Hi,

Why i refereed about the entry as they are some how related to the size and that will tell you that when you deleted/archive them via date how many were before deletion and after deletion . Also it will tell you the fact that when you delete/archive the records from the table how many new records have been created in that time frame and if no of entry delete is less than the number of entry inserted. That can justify the fact why size is not reducing.

But still i will try to see if i can get you query for one table first and if we can combine both.

Thanks

RishI Abrol

[ketxorange7]

Hi Rishi,

Thanks again for your reply. Actually, the size does reduce. For example, for year 2003, there is 3636 application logs entries. I can get this info by running table analysis on TAANA transaction. After I run sbal_delete on the year 2003, the 3636 entries were deleted, and I can see that around 200MB is reduced.

However, for the year 2004, there is 103,204 log entries, and after I run sbal_delete on the year 2004, only 50MB is reduced. Doesn't make sense right? That is why I am trying to find if there is any other way to know the size of the yearly entries in those tables, since running and monitoring on DB02 obviously won't give a precise size as new entries keeps coming as I am running the deletion.

Meanwhile, I am also working around with the query but haven't found any solution yet. It would be great if you can come out with the query that works. I really appreciate your assistance.

Regards,

Shakir