Dump Transaction Log hanging

[Private_Member_19084]

Hi experts

we have the following situation.

We have hanging DUMP TRANSACTION processes and if we don't get it in conroll the system might get stop the system, as the log gets filled.

The dump transaction is hanging with "Wait Event" "waiting for mass destruction to complete".

If I check the <SID>_BS.log I don't see ANY message regarding the dump it self.
What is really strange, as I also don't see in the log that it did start the backup.

The SQL Statement to be executed is "set notcount on set flushmessage on dump transaction <SID> to "<backup target>" with notify = client

Does anybody have an idea what I can do?

Kind regards

[christoph_heimann · Accepted solution]

Hi,

can you please tell us the ASE version which you are currently using?

-- select @@sbssav

-- select @@version

can you please check if there are any other hanging processes e.g. "reorg defrag ..."

Best regards

Christoph

[Private_Member_19084 · Accepted solution]

Hello Christoph,

DB Release is 15.7.0.136

No, I can not see any other process which is running similar long.

All the other processes are looking normal.

Can I check somewhere the "Locks Held", because I have locks held = 2 at the problem-process.

BTW, I've also opened a very high ticket now at SAP.

Kind regards

[christoph_heimann · Accepted solution]

Hello,

try the "sp_lock" system procedure.

Did you check if the dump tran process is blocked by another spid?

Best regards

Christoph

[Private_Member_19084 · Accepted solution]

How can I check if it is blocked?

[christoph_heimann · Accepted solution]

Hi,

try the following query:

select  

     spid

   , cmd

   , blocked

   , status

   , db_name(dbid) as dbname

   , hostprocess

   , hostname

   , suser_name(suid) as loginname

from

   master..sysprocesses

where

   spid = < spid of dump tran process >

The column "blocked" will contain the spid of the process which might block your dump tran process.

Best regards

Christoph

[Private_Member_19084]

Hello Christoph and thank you very much for your help.

I had a few hours phone call with SAP.

We did analyze the system and they also took some memory-dump, they will continue analyzing the probelm.

We also tried shutting down the BS-Server and also to force a kill´, without success.

Finally the answer from SAP was, that we have to reboot the ASE-System.

Hopefully I get an answer from SAP, what was causing this problem.

I give an update if I get one.

Thank you again

[Former Member]

Hi Anon 123,

I have few questions:

1. reboot here means OS reboot? was it suggested to shutdown sybase db first, did it stopped normally? wouldn't shutdown and start db again resolve the issue?

2. do you get the same issue again after OS reboot?

Thanks,

Henry

[Private_Member_19084]

Hello Henry,

no, restart of the DB should be enough.

I would also restart the BS server after the restart.

however, as the process is hanging you might have troubles with the stopdb.
It might be necessary to shutdown the DB with "shutdown with nowait".

Kind regards

[Former Member]

Hi Anon,

Thanks for your reply.

Kind Regards,

Henry

[Private_Member_19084]

I've killed now the os-process....but the DB-process is still hanging...any ideas?