Skip to Content

Query on BALDAT and BALHDR Table Size

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Nov 11, 2013 at 02:26 PM

    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 


    BC_SBAL.JPG (44.5 kB)
    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 11, 2013 at 03:38 AM

    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-are-deleted-but-database-size-does-not-change-in-sql-server-2008r2?forum=sqlsetupandupgrade

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • avatar image
    Former Member
    Nov 22, 2013 at 12:08 PM

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

    Cheers..!!! 😉

    Add comment
    10|10000 characters needed characters exceeded

    • 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