cancel
Showing results for 
Search instead for 
Did you mean: 

Deletion of entries in BC_MSG_LOG in PO 7.4

Former Member
0 Kudos

I have a question about delettion of entries in Java table BC_MSG_LOG and BC_MSG_AUDIT.

The reason is that our DB is now full as of infinite looping in one of our JMS-adaptors.

It has repeately taken the same message from the queue and put it back again over and over again.

As we are running PO 7.4 (no ABAP stack) we have not been able to find any notes or help on how to get rid of these faulty data in a short way.

Any clues?

Best regards

Flemming Bruun

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hello,

Check this note - 872388

Try to use below URL to directly delete messages from BC_MSG which will eventually delete messages fron both the tables...

https://host:port/MessagingSystem/job/reorgdb.jsp

Thanks

Amit Srivastava

Answers (1)

Answers (1)

former_member235282
Participant
0 Kudos

Hi Flemming

I am facing the same Issue, could you solve it using http://host:port/MessagingSystem/job/reorg db.jsp as Amit told you?

I appreciate your comments,

Regards,

DO

Former Member
0 Kudos

Hi Daniel,

I found out that the records we have to delete was not in status Finished. The script is only taking care of finished messages.

Therefore I had to delete the "frozen" records by hand as I know when the records was created.

As we are using MSSQL it was easy to delete them but I also found out that you have to do it in chunks.

The deletion takes a long time and the table will be locked in the meantime.

So to minimize the deletion time I deleted about 10000 records at the time.

It took about 2-5 min pr. chunk.

I made two small scripts to  help me counting and deleting the entries.

If you want to use them pls. make some test as this is just use-as-is. I can not garantee this is working on your DB.

As you might see I do not take care of status of the message but this is something you could do to narrow the search.

When the messages are deleted the cleaning job are taking care of the rest of the associated tables.

And made room in the database later to be shrinked.

It is a time consuming method but I made about 350 GB free in a weekend.

And all is still running 🙂

Best regards

Flemming Bruun

----Counting records:

select count(*)

  FROM [XXX].[SAPXXXDB].[BC_MSG_LOG]

  where LOG_TIME > '2014-08-27 00:00:00'

  and    LOG_TIME < '2014-10-15 00:00:00';

----Deleting records in chunks:

set nocount on;

declare @r INT;

declare @t INT;

set @r = 1;

set @t = 0;

WHILE @r > 0

    begin

        begin transaction;

            delete top (10000)

                FROM [XXX].[SAPXXXDB].[BC_MSG_LOG]

                where LOG_TIME > '2014-08-27 12:00:00'

                and    LOG_TIME < '2014-10-15 00:00:00';

            set @r = @@ROWCOUNT;

            set @t = @r + @t;

           

        commit transaction;

    end;

print @t;