SQL 2017 Cancelling Query

Transact-SQL
1

Hi.
I've executed the following query against a table with more than 100 million rows...

set nocount off
declare @message varchar(max)
declare @dtSK datetime

declare _qcurs cursor for
select gaDate
from dbo.dimDate
where gaDate between '2014-02-17' and '2018-03-06'
order by gaDate

open _qcurs
fetch next from _qcurs into @dtSK

while @@fetch_status = 0 begin
begin transaction
update fact_AllTraffic
set splitURL_YN = 'N'
,Split_1 = NULL
,Split_2 = NULL
,Split_3 = NULL
,Split_4 = NULL
,Split_5 = NULL
,Split_6 = NULL
,Split_Remainder = NULL
where gaDate = @dtSK
commit transaction

set @message = 'Completed: ' + cast(getdate() as varchar(100)) +' '+ cast(@dtSK as varchar(100))
RAISERROR( @message, 10,1) WITH NOWAIT
checkpoint

fetch next from _qcurs into @dtSK
end

close _qcurs
deallocate _qcurs

I purposefully designed the query to avoid a long transaction by updating table rows on a day by day basis. There is a more or less even distribution of rows by date.

I cancelled the query, in the expectation that the server would only rollback the transaction for the date it was currently updating, however, the query is taking a long time to cancel.
It's sat for an hour with a message "Cancelling Query..."

I appreciate I need to wait for the rollback, but would like to know...

  1. Why it's taking so long, given the way I designed the update.
  2. If I can estimate how long this will take to rollback.
  3. If I should make any changes to my code to avoid this happening.

Thanks.

2

If you want to do it right , in batches, see here

First of all, you have only one statement( the update) , so you don't need the starts of the transaction.

Probably you have an early execution that you tried to cancel , the cursor is still open, and transaction on it's open ... see what transactions are open, how many