August 9, 2012 at 5:45 am
Hi Guys,
Would appreciate a little help to see if this delete loop is doing what I want to:
I have 14,596,385 rows in a table and I need to delete data older than 12 months from today say. A count of this older data shows
select COUNT(*) from VIVO_CARD_TRANSACTION_LOG (nolock) -- 4,796,409
where VV_POST_DATE < '2011-08-09'
So I need to remove 4.7 Mil rows but didn't want to do it all on one transaction.
I tested the below code with a select statement where rowcount = 1000 for 10 loops and it pulled back 10 1000 row blocks so I figured it was working.
I then ran the code below and it hosed the system. I wanted to delete 10,000 rows then commit that, then do the next one for 100 loops.
I had to bounce the server and everything was rolled back. As the dlete was running, I was chacking with a count (nolock) and was into 400K deletes before things went kaput. Had out of memory in the error log also "Message
There is insufficient system memory in resource pool 'internal' to run this query."
Anybody see anything wrong here?
Thanks
P
set nocount on
select 'Start at...', getdate()
GO
declare @NumtoProcess INT
DECLARE @i int
-- DECLARE @ean char(13)
SELECT @i = 0
select @NumtoProcess = 100
set rowcount 10000
WHILE @i <= @NumtoProcess
BEGIN
BEGIN TRANSACTION
delete from VIVO_CARD_TRANSACTION_LOG
where VV_POST_DATE < '2011-08-11'
if @@error <> 0
BEGIN
ROLLBACK TRANSACTION
END
COMMIT Transaction
SELECT @i = @i + 1
END
GO
select 'End at...', getdate()
GO
August 9, 2012 at 6:19 am
First, you shouldn't use SET ROWCOUNT to control the number of rows affected by your DELETE statement. This functionality has been depreciated and may be removed in future versions of SQL Server.
Please read the following article, it will help you develop a process that will allow you to delete a large number of records (rows) from your table and control the size of your transaction log.
August 9, 2012 at 7:06 am
Thanks Lynn.
Apart from the deprecated code, do you or anybody else see any issues with my delete routine?
Why did the whole lot roll back?
P
August 9, 2012 at 7:10 am
I don't have an environment to test on at the moment, but I suspect as it's all submitted as one batch, there's an outer (implicit) transaction that's for the whole batch, then the inner transactions that are being started and committed by your loop, hence the data is still uncommitted.
You might want to try (as well as changing to DELETE TOP syntax), scheduling this in a SQL Agent job that runs on an interval, rather than using a while loop...
August 9, 2012 at 8:24 am
Lynn Pettis (8/9/2012)
First, you shouldn't use SET ROWCOUNT to control the number of rows affected by your DELETE statement. This functionality has been depreciated and may be removed in future versions of SQL Server.
I disagree. If it still works, why should you stop using it? Especially in one off scripts.
I still use it sometimes but I'm fully aware that its likely to be removed in a future version. Of course, i wouldnt use it in current development
August 9, 2012 at 8:27 am
MysteryJimbo (8/9/2012)
Lynn Pettis (8/9/2012)
First, you shouldn't use SET ROWCOUNT to control the number of rows affected by your DELETE statement. This functionality has been depreciated and may be removed in future versions of SQL Server.I disagree. If it still works, why should you stop using it? Especially in one off scripts.
I still use it sometimes but I'm fully aware that its likely to be removed in a future version. Of course, i wouldnt use it in current development
Mostly because somebody else will copy this in the future, or you will forget and do it yourself after it is removed.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 9, 2012 at 8:30 am
So we mentioned the deprecated code but you are still using the "old style" error handling by checking @@ERROR. You should use the newer try-catch construct instead. It is far more robust. I think the following should work.
set nocount on
select 'Start at...', getdate()
GO
declare @NumtoProcess INT
DECLARE @i int
SELECT @i = 1 --need to set to one so we can enter the loop
WHILE @i > 0 BEGIN
BEGIN TRANSACTION
begin try
delete top (1000)
from VIVO_CARD_TRANSACTION_LOG
where VV_POST_DATE < '2011-08-11'
select @i = @@ROWCOUNT
end try
begin catch
ROLLBACK TRANSACTION
select 'You have an error, what do you want to do with it?'
set @i = 0 --if you want to keep processing batches just remove this.
end catch
END
GO
select 'End at...', getdate()
GO
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 9, 2012 at 8:35 am
Sean Lange (8/9/2012)
MysteryJimbo (8/9/2012)
Lynn Pettis (8/9/2012)
First, you shouldn't use SET ROWCOUNT to control the number of rows affected by your DELETE statement. This functionality has been depreciated and may be removed in future versions of SQL Server.I disagree. If it still works, why should you stop using it? Especially in one off scripts.
I still use it sometimes but I'm fully aware that its likely to be removed in a future version. Of course, i wouldnt use it in current development
Mostly because somebody else will copy this in the future, or you will forget and do it yourself after it is removed.
At which point it'll be fixed.
My point is that for one off scripts as is the case here there is no harm in doing it. It isn't wrong so why suggest they rewrite it to meet standards. Simply raise the point and suggest an alternative.
August 9, 2012 at 8:43 am
Thanks very much Sean.
I'll try that on a test server this time.
P
August 9, 2012 at 8:48 am
Sean Lange (8/9/2012)
So we mentioned the deprecated code but you are still using the "old style" error handling by checking @@ERROR. You should use the newer try-catch construct instead. It is far more robust. I think the following should work.
set nocount on
select 'Start at...', getdate()
GO
declare @NumtoProcess INT
DECLARE @i int
SELECT @i = 1 --need to set to one so we can enter the loop
WHILE @i > 0 BEGIN
BEGIN TRANSACTION
begin try
delete top (1000)
from VIVO_CARD_TRANSACTION_LOG
where VV_POST_DATE < '2011-08-11'
select @i = @@ROWCOUNT
end try
begin catch
ROLLBACK TRANSACTION
select 'You have an error, what do you want to do with it?'
set @i = 0 --if you want to keep processing batches just remove this.
end catch
END
GO
select 'End at...', getdate()
GO
One small change:
set nocount on
select 'Start at...', getdate()
GO
declare @NumtoProcess INT
DECLARE @i int
SELECT @i = 1 --need to set to one so we can enter the loop
WHILE @i > 0 BEGIN
begin try
BEGIN TRANSACTION
delete top (1000)
from VIVO_CARD_TRANSACTION_LOG
where VV_POST_DATE < '2011-08-11';
COMMIT TRANSACTION; -- If the DELETE errors, this will be skipped and the ROLLBACK in the CATCH will run
select @i = @@ROWCOUNT;
end try
begin catch
ROLLBACK TRANSACTION
select 'You have an error, what do you want to do with it?'
set @i = 0 --if you want to keep processing batches just remove this.
end catch
END
GO
select 'End at...', getdate()
GO
August 9, 2012 at 8:51 am
MysteryJimbo (8/9/2012)
Lynn Pettis (8/9/2012)
First, you shouldn't use SET ROWCOUNT to control the number of rows affected by your DELETE statement. This functionality has been depreciated and may be removed in future versions of SQL Server.I disagree. If it still works, why should you stop using it? Especially in one off scripts.
I still use it sometimes but I'm fully aware that its likely to be removed in a future version. Of course, i wouldnt use it in current development
Here is why, it has been depreciated and you should start using the newer methods in your development. This may be a one off process but that is no reason to use a depreciated way of doing work. This is the perfect time to start using the new methods so that you can learn and adapt them as needed.
August 9, 2012 at 9:02 am
Lynn Pettis (8/9/2012)
Sean Lange (8/9/2012)
So we mentioned the deprecated code but you are still using the "old style" error handling by checking @@ERROR. You should use the newer try-catch construct instead. It is far more robust. I think the following should work.
set nocount on
select 'Start at...', getdate()
GO
declare @NumtoProcess INT
DECLARE @i int
SELECT @i = 1 --need to set to one so we can enter the loop
WHILE @i > 0 BEGIN
BEGIN TRANSACTION
begin try
delete top (1000)
from VIVO_CARD_TRANSACTION_LOG
where VV_POST_DATE < '2011-08-11'
select @i = @@ROWCOUNT
end try
begin catch
ROLLBACK TRANSACTION
select 'You have an error, what do you want to do with it?'
set @i = 0 --if you want to keep processing batches just remove this.
end catch
END
GO
select 'End at...', getdate()
GO
One small change:
set nocount on
select 'Start at...', getdate()
GO
declare @NumtoProcess INT
DECLARE @i int
SELECT @i = 1 --need to set to one so we can enter the loop
WHILE @i > 0 BEGIN
begin try
BEGIN TRANSACTION
delete top (1000)
from VIVO_CARD_TRANSACTION_LOG
where VV_POST_DATE < '2011-08-11';
COMMIT TRANSACTION; -- If the DELETE errors, this will be skipped and the ROLLBACK in the CATCH will run
select @i = @@ROWCOUNT;
end try
begin catch
ROLLBACK TRANSACTION
select 'You have an error, what do you want to do with it?'
set @i = 0 --if you want to keep processing batches just remove this.
end catch
END
GO
select 'End at...', getdate()
GO
DOH! Missed the commit. Thanks Lynn.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 9, 2012 at 9:06 am
Thanks Lynn. I spotted that also. the missing "COMMIT TRAN "
Woo hoo 🙂
P
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply