To delete rows without locking a table which has say more than 100 million rows to be deleted. Or a large table with archived data has to be deleted. You can use the below function to do this deletion.
DECLARE @Count INT Declare @for_delete INT Declare @chunk_size INT SELECT @chunk_size=100000 SELECT @Count = 0 select @for_delete=count(*) from [Your big table] where [field name] > getdate () - 180 While (@Count < @for_delete) BEGIN SELECT @Count = @Count + @chunk_size BEGIN TRAN DELETE top(@chunk_size) FROM [Your big table] where [field name] > getdate () - 180 COMMIT TRAN END