delete in batch problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

delete in batch problem

Author

Topic

rbaldwin
Starting Member

4 Posts

Posted - 2010-09-09 : 14:07:00

i have a table with 46.5 million rows from which i need to delete 4 million based on a date column.

i've written a query to loop and delete top x in batches.

my probelm is that after the rows are deleted i query the table to see how many rows remain, but the result from the query indicates that no rows have been deleted. Yet, while it is looping i do get the message x rows affected.

So, it seems that the looping and deleteing is working yet the count isn't being updated??

throughout my loop the remaining row count does not change??

SET @Message = 'Clean up of IBMSLA_Vulns Starting at: ' + convert(varchar,getDate())
raiserror (@Message,10,1) with nowait
DECLARE @NoOfRowsToDelete int
SET @NoOfRowsToDelete = 2000

DECLARE @CurrentRowCount int

-- Count the number of rows in the table
SELECT @CurrentRowCount = (select count(*) from IBMSLA_Vulns where retentionDate < getDate())

SET @Message = 'Total # of Rows to delete: ' + convert(varchar,@CurrentRowCount)
raiserror (@Message,10,1) with nowait

-- Loop and delete records in small bits till the rowcount is 0
WHILE(@CurrentRowCount > 0)
BEGIN
BEGIN Tran
DELETE TOP (@NoOfRowsToDelete) FROM IBMSLA_Vulns
COMMIT TRAN
-- RECOUNT
SELECT @CurrentRowCount = (select count(*) from IBMSLA_Vulns where retentionDate < getDate())

SET @Message = 'Remaining Rows to delete: ' + convert(varchar,@CurrentRowCount)
raiserror (@Message,10,1) with nowait
WAITFOR DELAY '00:01:00';--HH:MM:SS

END

SET @Message = 'Finished at: ' + convert(varchar,getDate())
raiserror (@Message,10,1) with nowait

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-09 : 14:59:36

There is no WHERE clause on your DELETE so you are, apparently, deleting random rows that do nto meet your date criteria.

rbaldwin
Starting Member

4 Posts

Posted - 2010-09-10 : 08:27:40

Yeah, thanks, i did see that eventually.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources