September 12, 2002 at 2:59 pm
If a table T1 has 800 MB data with about 300,000 records. I need to purge this table without using truncate. I am using following code
CHECKPOINT
SET ROWCOUNT 1000
SET @SELECT_QStr = 'select count(*) from T1'
SET @QStr = 'delete from T1'
BEGIN TRANSACTION
Exec (@SELECT_QStr)
WHILE @@ROWCOUNT > 0
BEGIN
Exec (@QStr)
END
COMMIT
SET ROWCOUNT 0
But it gives me transact log full error even though transaction log has enough space 500 MB to hold 1000 records at a time.
Can somebody find reason for this. Suggest any and better and faster solution.
Thanks
Gopal
September 12, 2002 at 3:36 pm
I'm guessing since you are executing your delete command with exec (@Qstr) that possible this starts a new batch and your "SET ROWCOUNT 1000" is not controlling this new batch, so in reality you are trying to delete all 300,000.
Possible you should change your delete to 'delete t1 from (select top 1000 * from t1) as t2
where t1.id = t2.id ' to ensure only 1000 rows are deleted.
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 12, 2002 at 4:30 pm
or preface your @Qstr with "SET ROWCOUNT 1000", probably be quicker than doing a self join.
September 12, 2002 at 4:43 pm
I'll agree with the self join slowing down the process.
Another option to make sure the new batch does in fact only delete 1000 records would be to set your @Qstr to
SET @SELECT_QStr = 'set rowcount 1000' + char(13) + 'select count(*) from T1'
Also noted that possibly your Begin tran and commit should be inside the while loop. Looks like you do a lots of deletes commands prior to the commit. Maybe this is your real problem. Need to do commits after ever 1000 delete to keep the log clean.
Something like this:
CHECKPOINT
SET ROWCOUNT 1000
SET @SELECT_QStr = 'select count(*) from T1'
SET @QStr = 'delete from T1'
Exec (@SELECT_QStr)
WHILE @@ROWCOUNT > 0
BEGIN
BEGIN TRANSACTION
Exec (@QStr)
COMMIT
END
SET ROWCOUNT 0
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply