Saturday, 30 December 2017

Blocking on sqlserver

To check blocking


select * from sys.sysprocesses where blocked <> 0


head blocker

++++++++++

declare @handle binary(20), @spid int

select top 1 @spid = blocked
from master..sysprocesses a
where a.blocked != 0 
and a.blocked not in (select spid from master..sysprocesses b where blocked != 0)

if @spid is not null
begin
  select @handle = sql_handle from master..sysprocesses where spid = @spid

  exec sp_who2 @spid

  dbcc inputbuffer (@spid)

select lastwaittype from sysprocesses where spid=@spid
  select * from ::fn_get_sql(@handle)

end  

No comments:

Post a Comment

Featured post

Restircted session due to sync filed with ora-65177

Application is unable to connect the database due to restricted session. sql> show pdbs; SQL> show con_name CON_NAME -----------------...