Background story: I've needed to expand user database. And that action caused lots of blocked sessions. I have tried to find out why alter database command can not be done online, and what is causing these locks. In that experiment, I've needed some script which will help me catch these blocks.
Solution: I have been using this script for monitoring. It is started from one session, and from other session I've been executing command which I wanted to monitor.
declare @i int
set @i=0
while @i<20
begin
select * from sysprocesses where blocked <> 0
--select * from syslocks
waitfor delay "0:00:02"
set @i = @i + 1
end
Change duration and frequency for Your own need. This script will run 20 times, and on every two seconds check if there are blocked sessions.
Conclusion: More important thing is question if alter database expansion is blocking sessions or not. What I've found out is that when run in parallel - alter database, and transaction log backup, that it is causing lots of blocking sessions in user database. So, I've managed to run database expansion script "online", when I turned off regular transaction log backup job during database expansion.
