Speeding up a huge delete
-
Ya we'll pick it up in the morning.
L8r
! -
Table name and the relevant column are in the opening post...that script in two versions, "Run_this_test_first" and the second "Adjust_Rowcount_According_to-Result_and-Run" would be very handy...
Anyway, night all!!
JB
-
During DELETE only LOG file is growing. So, this may lead to situation when DB run out of space.
So, you need to shrink it just not to allow to grow.
.mdf will not grow during the process, so you may leave it alone.
You will shrink entire database after whole thing is completed.
_____________
Code for TallyGenerator -
May I also suggest a job that will periodically delete useless data from that table?!?!
-
Morning/Afternoon/Evening fellow SQL Illuminati..
The last DBA set up some scheduled jobs to pipe out data and then delete.
Anyway, some help with that rowcount syntax would be very welcome!
JB
-
No seriously :
how to build the shrinkfile command :
Select FileID from YourDbNameHere.dbo.SysFiles where name like '%_Log%'
--gives you the first parameter
SP_helpdb 'YourDbNameHere'
--in the 2nd resultset, check the size of the log file and divide it by 1024.
--this will be the 2nd paramter
Select 1098432 / 1024
--the no truncate option is to not release the free space to the OS (because you'll need it for the next delete).
DBCC SHRINKFILE (2, 1072, NOTRUNCATE)
--script
SET ROWCOUNT 10000
DECLARE @rc AS INT
SET @rc = 1
WHILE @rc > 0
BEGIN
DELETE FROM dbo.webproxylog where week_number BETWEEN 36 AND 40
SET @rc = @@ROWCOUNT
BACKUP LOG WITH TRUNCATE_ONLY
DBCC SHRINKFILE (FileID, SizeInMB, NOTRUNCATE)
WAITFOR DELAY 00:00:01 --give at elast 1 second for other operations to complete, I'd suggest 2 seconds if you're not in a big hurry
END
SET ROWCOUNT 0
-
So how was the night???
-
Well, I made a lot of insertions, processor usage (on both nodes in our cluster) went up to or near 100% for about 45 minutes, and then finally I pumped out huge amounts of data.
The process then repeated twice more with half-hourly intervals before falling over after the third data-pump, and the rest was simply one long maintenance window...
Lemme take a look at this code..
-
Got some syntax issues...and discovered that I have about 21Kb left...
Server: Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'WITH'.
Server: Msg 170, Level 15, State 1, Line 19
Line 19: Incorrect syntax near '00'.
Here's the current code:
SET ROWCOUNT 10000
DECLARE @rc AS INT
SET @rc = 1
WHILE @rc > 0
BEGIN
DELETE FROM dbo.webproxylog where week_number BETWEEN 36 AND 40
SET @rc = @@ROWCOUNT
BACKUP LOG WITH TRUNCATE ONLY
DBCC SHRINKFILE (2, 34232, NOTRUNCATE)
WAITFOR DELAY 00:00:01 --give at elast 1 second for other operations to complete, I'd suggest 2 seconds if you're not in a big hurry
END
SET ROWCOUNT 0
-
Sorry I forgot to test this code... would have been simpler for you :
SET ROWCOUNT 10000
DECLARE @rc AS INT
SET @rc = 1
WHILE @rc > 0
BEGIN
DELETE FROM dbo.webproxylog where week_number BETWEEN 36 AND 40
SET @rc = @@ROWCOUNT
BACKUP LOG DbNameHere WITH TRUNCATE_ONLY
DBCC SHRINKFILE (2, 34232, NOTRUNCATE)
WAITFOR DELAY '00:00:01' --give at elast 1 second for other operations to complete, I'd suggest 2 seconds if you're not in a big hurry
END
SET ROWCOUNT 0
-
This thread is onw officially rated R
. -
Is this one safe to run overnight?
-
Depends on how busy is the server. You can start with a 3 secs wait delay. Then change accordingly to how the server behaves. It shouldn't slow it down too much but I don't know for sure. If you see it's not slowing anything down, then you can lower the wait time to 2 seconds then 1. Then it can run for a week without disturbing anyone.
-
Actually it's safe to run around o'clock. That's what those 1sec pauses are about.
You may start performance monitor and than run the code. See if it have added any significant load. Not suppose to.
If so, you may leave it running and switch to loading your processor up to 100%.
_____________
Code for TallyGenerator -
Cheers guys, the delete is running as we speak, launched it this morning, the speed is about 28,000 rows per minute and seemingly accelerating, should be done by Saturday morning (CET)
I'm not even sure about how the DB gets loaded, all I can see is a bunch of jobs/packages that export data from ISALOG, but however, whatever, this delete HAS to go ahead - all other considerations are void.
Thanks for the help chaps, it's been an education in the fineries of the RDBMS!!
JB
Viewing 15 posts - 16 through 30 (of 47 total)
You must be logged in to reply to this topic. Login to reply