SQL Query Delete top 4000 rows when row count > 10K looping to reduce row count to 10K

Question

SQL Query Delete top 4000 rows when row count > 10K looping to reduce row count to 10K

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

register 33943 SSC Veteran Points: 277 More actions · Answer 1

1. How can I make this loop until the IF statement is false?
2. I don't care what records are deleted and I just want to keep the row count at 100K
3. I want to commit the transaction for every 4K rows deleted before continuing to delete the next 4K rows

USE Alarms

DECLARE @tableRows int

SELECT @tableRows=COUNT(1) FROM [GW-PLC].[AllEvent]
IF(@tableRows>100000) --checking if your table has more than 100,000 records

BEGIN -- start delete
DELETE TOP(4000) FROM [Alarms].[GW-PLC].[AllEvent]

END

Jonathan AC Roberts SSCoach Points: 18356 More actions · Answer 2

register 33943 - Tuesday, November 20, 2018 7:46 AM
1. How can I make this loop until the IF statement is false?
2. I don't care what records are deleted and I just want to keep the row count at 100K
3. I want to commit the transaction for every 4K rows deleted before continuing to delete the next 4K rows
USE Alarms
DECLARE @tableRows int
SELECT @tableRows=COUNT(1) FROM [GW-PLC].[AllEvent]
IF(@tableRows>100000) --checking if your table has more than 100,000 records
BEGIN -- start delete
DELETE TOP(4000) FROM [Alarms].[GW-PLC].[AllEvent]
END

DECLARE @TotalRowsToDelete int DECLARE @RowsToDelete int SELECT @TotalRowsToDelete = COUNT(*)-100000 FROM [GW-PLC].[AllEvent] WHILE @TotalRowsToDelete > 0 BEGIN -- start delete SET @RowsToDelete = IIF(@TotalRowsToDelete>=4000,4000,@TotalRowsToDelete) BEGIN TRANSACTION DELETE TOP(@RowsToDelete) [Alarms].[GW-PLC].[AllEvent] COMMIT TRANSACTION SET @TotalRowsToDelete -= @RowsToDelete END

register 33943 SSC Veteran Points: 277 More actions · Answer 3

Thank you, that works perfectly and is easy to understand what the script is doing
GOOD JOB!!!
Will anything have to change when I move the recovery model to Simple? We don't need a way to recover the database.

pietlinden SSC Guru Points: 63503 More actions · Answer 4

???

Recovery models don't have anything to do with what SQL works in a database. That's controlled by the Compatibility Level the database is set to.

register 33943 SSC Veteran Points: 277 More actions · Answer 5

understood. I reviewed a script that had a command at the end and the command had to change according to the recovery model. Can't find it and I don't remember what it was.
No worries, thanks.

pietlinden SSC Guru Points: 63503 More actions · Answer 6

Of course, if you want to recover the records you deleted, and you're in Simple Recovery, you're out of luck. You'd have to restore an old copy of the database under a different name... but if you don't need recovery, then Simple will work.

register 33943 SSC Veteran Points: 277 More actions · Answer 7

register 33943

SSC Veteran

Points: 277

November 20, 2018 at 3:08 pm

#2014152

Thank You to everyone for all the help.

register 33943 SSC Veteran Points: 277 More actions · Answer 8

Here is the script for the change of statement if in simple mode. The reason I mention this is because I have changed our database to simple mode. When I run the delete statement 4K at a time I keep seeing Wait type WriteLog. I am currently backing up the database to see if that changes things. Since I have not done that since changing to simple mode.

DECLARE @Deleted_Rows INT;SET @Deleted_Rows = 1;WHILE (@Deleted_Rows > 0)  BEGIN BEGIN TRANSACTION -- Delete some small number of rows at a time   DELETE TOP (10000)  LargeTable    WHERE readTime < dateadd(MONTH,-7,GETDATE())   SET @Deleted_Rows = @@ROWCOUNT; COMMIT TRANSACTION CHECKPOINT -- for simple recovery modelEND

Do I need to add the Checkpoint statement to the delete statement created in the previous post since I am running in simple mode?
Does this command bypass the log or just tell it to write the data to the database

Jonathan AC Roberts SSCoach Points: 18356 More actions · Answer 9

register 33943 - Wednesday, November 21, 2018 9:41 AM
Here is the script for the change of statement if in simple mode. The reason I mention this is because I have changed our database to simple mode. When I run the delete statement 4K at a time I keep seeing Wait type WriteLog. I am currently backing up the database to see if that changes things. Since I have not done that since changing to simple mode.
DECLARE @Deleted_Rows INT;SET @Deleted_Rows = 1;WHILE (@Deleted_Rows > 0)  BEGIN BEGIN TRANSACTION -- Delete some small number of rows at a time   DELETE TOP (10000)  LargeTable    WHERE readTime < dateadd(MONTH,-7,GETDATE())   SET @Deleted_Rows = @@ROWCOUNT; COMMIT TRANSACTION CHECKPOINT -- for simple recovery modelEND
Do I need to add the Checkpoint statement to the delete statement created in the previous post since I am running in simple mode?
Does this command bypass the log or just tell it to write the data to the database

In Simple mode the transaction log is still written to. Nothing you can do about that.
Checkpoint writes dirty pages to disk so it can recover in the case of a shutdown or crash. It might slow things down if you are issuing one after every 4000 rows deleted.

register 33943 SSC Veteran Points: 277 More actions · Answer 10

register 33943

SSC Veteran

Points: 277

November 21, 2018 at 10:30 am

#2014267

ok, thanks