Tracking SQL Server lock overflow in Dynamics AX Batches

When looking at a SQL Server 2008 R2 locking/blocking problem in an Dynamics AX 2009 installation, I wanted to see what was going on with regards to locks taken by the specific SPID allocated to the AX Batch. I created a very simple script, that could also be useful in other situations. In this case it could be concluded that the cursor based batch seemed to lock row by row in a particular table, until all rows where locked and the batch completed. Needless to say, this caused problems for other processes during that time. The script tells you the number of locks for a specified SPID, repeated x number of times, with a 5 seconds interval.

Enjoy!   declare @locks varchar(100) declare @msg varchar(100) declare @revs int declare @i int declare @spid int select @revs = 10 select @i = 1 select @spid = 107   while @i <= @revs begin select @locks =count(*)  from sys.dm_tran_locks where request_session_id = @spid; select @msg =cast(‘Number of locks for SPID ‘as varchar(100)) + convert(varchar(10),@spid) + ‘ = ‘ select @msg =cast(@msg as varchar(100)) + convert(varchar(10),@locks ) select @msg RAISERROR ( @msg, 1, 1) with NOWAIT waitfor delay ’00:00:5′ select @i = @i + 1 end