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.
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,
(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
) 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
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
|spid||the current session|
|object name||related table|
|text||the sql command from this session|
|batch duration||running time...|
|program_name||name from program from this session|
|hostname||the name of host|
|loginname||SQL user name|
|blocked||blocked by ...|
here you can see all information from the session, which block this session
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
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:
Find out, which Spid causes the problem (see above)
Determine station name, Program name using the above mentioned SQL-Statements
- 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:
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.
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.
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:
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
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.
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.