In addition to this I am again wondering if the code in a transaction and loop release the tempdb allocations. For the template below:
SET NOCOUNT ON;
WHILE EXISTS(....)
BEGIN
BEGIN TRY;
BEGIN TRANSACTION;
--
UPDATE TOP(X) ...
--
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN;
ROLLBACK TRANSACTION;
END;
THROW;
END CATCH;
WAITFOR DELAY '00:00:01';
END;
In sp_whoisactive I can see millions of tempdb allocations. The question is can these allocations cause tempdb spill or they are summarized and the allocations are release after each transaction commit?
