problem deleting records

Last Post 28 Aug 2006 11:15 AM by trans53. 9 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
trans53
New Member
New Member

--
24 Aug 2006 05:48 PM
Hi guys,

This is the problem i have:

I do have a 130 tables setup to delete records less than specific date time using the stored procedure.

All tables are purging data without any problems, but ONE has always give me less number of rows than suppose to be. I can't find the reason why. For example if 44 M rows should be deleted then only 8M in the final result. The strange thing is that i have a tables with more rows to be deleted and all ok with those.

I narrow it down to the SET ROWCOUNT statement. When it's not used then all ok with this table, but when i added back i got diff. result. Below is the loop how the SP calculates and do purge:

SET ROWCOUNT 50000

WHILE 1 = 1

BEGIN
delete from table_name
where date_time_field < cast( '07/22/2005' as datetime)

Select @CountCheck = @@RowCount

SET @RowCnt = @RowCnt + @CountCheck

IF @CountCheck = 0

BREAK
END

SET ROWCOUNT 0


What can be a problem for this specific table?

Thanks
SQLUSA
New Member
New Member

--
26 Aug 2006 01:08 AM
The logic seems to be OK. Probably hit an MS issue. You are talking about M - millions of rows?

Microsoft introduced DELETE TOP 50000 (or other) ....with SQL Server 2005. This is supposed to be reliable.

Another way to delete is select * into what you want to keep combined with renaming. It maybe faster.

Kalman Toth, Database, Data Warehouse and BI Architect
http://www.sqlusa.com/order2005/
The Best SQL Server 2005 Training in the World
trans53
New Member
New Member

--
26 Aug 2006 05:08 AM
Thank you for reply,

this is very interesting, i fixed the problem by creating exact copy of the original table, moving 75 million rows over, truncating the original table and then moving data back. That fixed the problem.

But still i don't know what happened with the table.
trans53
New Member
New Member

--
27 Aug 2006 09:09 AM
Yes, before i moved records i checked table with dbcc checktable. No errors retured. Still a mistery to me.
trans53
New Member
New Member

--
28 Aug 2006 11:15 AM
Just updated statistics, did not helped in this case. thank you
trans53
New Member
New Member

--
28 Aug 2006 01:32 PM
None of the above. Like i said it's very strange. It does work correct only when i move data into temp table, truncate original table and move data back. All this on sql server 2000 sp3
SQLUSA
New Member
New Member

--
04 Sep 2006 06:02 AM
You should be on sp4 with SQL Server 2000.

Let us know if still occurs.

Kalman Toth, Database, Data Warehouse and BI Architect
http://www.sqlusa.com
The Best SQL Server 2005 Training in the World
trans53
New Member
New Member

--
04 Sep 2006 06:16 PM
We should be on SP4, but after reading reviews about problems in SP4 we decided not to upgrade.
trans53
New Member
New Member

--
05 Sep 2006 02:51 PM
Where i can find the SQL 2000 post SP4 Hotfix package for download?

Thanks
trans53
New Member
New Member

--
06 Sep 2006 06:06 PM
Thank you


Acceptable Use Policy
---