Help with Begin Tran/Commit Tran.

Help with Begin Tran/Commit Tran.

Post by arunrajmoh » Mon, 29 Nov 2004 06:30:31


Hi,

I have a table (5 million rows) containing duplicate records (around
500K) that needs to be deleted.
The following sp filled up the transaction log though I gave a begin
tran /end tran after each delete. I do not have enough privileges to
dump transaction logs either.
Any help to rectify the flaw would be appreciated!

The code

CREATE PROCEDURE delete_duplicate
AS

SET NOCOUNT ON

DECLARE @iCount int
DECLARE @iCorrAccNo int
DECLARE @dtValueDate datetime
DECLARE @iStock_ID int

DECLARE c1 cursor FOR
SELECT COUNT(*) tmp_Count,
corr_acc_no, value_date, stock_id
FROM arc_svbl
GROUP BY corr_acc_no, value_date, stock_id
HAVING COUNT(*) > 1

OPEN c1
FETCH c1 into @iCount, @iCorrAccNo, @dtValueDate, @iStock_ID
WHILE (@@sqlstatus = 0)
BEGIN
SELECT @iCount = @iCount - 1
SET ROWCOUNT @iCount
BEGIN TRAN
DELETE arc_svbl WHERE
corr_acc_no = @iCorrAccNo AND
value_date like @dtValueDate AND
stock_id = @iStock_ID
COMMIT TRAN
IF ( @@sqlstatus <> 0 )
BEGIN
RETURN
END
FETCH c1 into @iCount, @iCorrAccNo, @dtValueDate, @iStock_ID
END
CLOSE c1
DEALLOCATE cursor c1
RETURN 0


Thanks!
 
 
 

Help with Begin Tran/Commit Tran.

Post by Frank Hame » Mon, 29 Nov 2004 08:08:35

"Rhodeo" wrote
Rough hack of your code follows - no need for a BEGIN END Tran as the Delete
statement runs in its own transaction. Just delete rows in batches of
10,000 (or less depending on the row size and the amount of log space).

Cheers
Frank.

SET ROWCOUNT 10000
WHILE @iCount > 0
BEGIN
SELECT @iCount = @@rowcount
END
SET ROWCOUNT 0

 
 
 

Help with Begin Tran/Commit Tran.

Post by Anthony Ma » Mon, 29 Nov 2004 14:11:05


Each delete is atomic, you don't need the transaction wrapper.

If you don't have the privileges, you'd better get your DBA
involved. There's not much you can do otherwise.


There's no real need to write this code. A simpler approach
would be to exploit the ignore_dup_row of a nonunique clustered
index. Since this applies during inserts, you'd need to copy
the table. This will require your DBA again. I'd suggest
coping to another database with a larger log and then copying
the resultant rows back. I assume you know what other steps are
required (truncate table etc.). You could try tempdb if its
large enough. Make sure you have the data saved, of course.

-am ?2004
 
 
 

Help with Begin Tran/Commit Tran.

Post by arunrajmoh » Tue, 30 Nov 2004 10:24:34

Thanks folks!