Beas standstill

Sometimes Beas or SBO may be blocked (Program crash, or hang up. If Beas is ended, SBO keeps running. Reason may be a deadlock on the SQL-Server.

IMPORTANT:
Before searching for the error, restart the SQL-Server. Especially after updating SBO a restart of the SQL-Servers is recommended, to release memory.

If Beas cannot be started at all, on the SQL-server using a SQL-command you can determine, which station is causing the deadlock.

select P.spid ,OBJECT_NAME(rsc_objid ,rsc_dbid) AS 'OBJECT_NAME', SQLTEXT.Text,right(convert(varchar,  dateadd(ms, datediff(ms, P.last_batch, getdate()), '1900-01-01'),
121), 12) as 'batch_duration' , P.program_name , P.hostname , P.loginame,
p.blocked,
(select rtrim(program_name)+' '+rtrim(p2.hostname) +' '+rtrim(p2.loginame)+' Net-Address: '+p.net_address+' SQL:'+SQLTEXT.text from master.dbo.sysprocesses P2
CROSS APPLY sys.dm_exec_sql_text(P2.SQL_HANDLE) SQLTEXT
where p2.spid=p.blocked
) as lockedfrom
from master.dbo.sysprocesses P 
inner join sys.syslockinfo L on l.req_spid = p.spid
CROSS APPLY sys.dm_exec_sql_text(P.SQL_HANDLE) SQLTEXT
where
P.status not in ('background', 'sleeping') and P.cmd not in ('AWAITING COMMAND'
,'MIRROR HANDLER'  ,'LAZY WRITER' ,'CHECKPOINT SLEEP' ,'RA MANAGER') 
and OBJECT_NAME(rsc_objid ,rsc_dbid) > ''
order by batch_duration desc

You see a table with follow columns

Attention

spid 1 can locked by spid 2
spid 3 can be locked by spid 2

in this case spid 1 block spid 2 and 3

Simulate this with follow command

1. Execute begin transaction

begin transaction
select * from Beas_sys_setup with (TABLOCK, HOLDLOCK)
2. start Beas -> Beas can't start, it's freezed.

Before creating a ticket
If a blockage occurs several times:

1. Find out, which Spid causes the problem (see above)
2. Determine station name, Program name using the above mentioned SQL-Statements
3. Find out what the user did at last

Spid-Id is the number of the originator (see number 1)

For each blockage information needs to be determined new. Only this way you an find out if it is a hardware or a software problem and what to do.

Important: only the originator is of interest

What could be possible reasons? 

Next to software errors the hardware can cause deadlocks: 

•  network overload
•  server or SQL-service overload,
•  not enough memory or operation in 32-Bit-environment
•  other processes running in parallel ( eg. backup)
•  faulty cables, network adapters, broken switch/hub
•  faulty drivers, wrong configuration, virus 

There may be various reasons. It is often not that easy to find the reason, the support can only help, if the client analyzed the problem in detail before. 80% of all cases were related to hardware problems.

Example 1:

Always the same station is the originator although the same operations are performed on other stations. The error causing SQL-statements are different. Then, the actual reason is the work station itself, the connected network cable or the hub.

Example 2:

Various work stations are originators but the blocking SQL-statement is always the same. In this case, one can assume that a software error is the cause.

Troubleshooting without restart of SQL-Server

First, the real cause needs to be determined. Then, the causing statement can be removed using:
kill 30
Ends the connection for Spid 30.

There is no need to restart the SQL-Server. Beas and SBO re-establish the connection immediately. It may be, that on the affected work Station SBO or Beas needs to be restarted.

to release the block, use kill command
kill 52
closes connection for Spid 52. Now, the SQL-Server does not need to be re-started after a malfunction. Beas usually re-establishes the connection after issuing an error message.

Further measures

in case of SQL-error: select isnull(max(eventnumber),0) from Beas_event where touser=...

This statement checks regularly, if new messages are available. But many users do not work with the message system of SBO or Beas

In this case you can disable per user:

 SAP - Administration - System Initialization - General Settings - Services - "Inbox for new messages" please disable for each user.

Alternatively (without guarantee): Deactivation for all users with the following SQL-Statement:
update ousr set shownewmsg='N'
If necessary it can be activated again.