August 5, 2010 at 2:25 am
We have a very huge table in Development Database.
So Each night, we want to delete data from this table in small batches.
Here are the specs:
•The job should start at midnight and should stop at 6:00 AM.
•It should delete rows from the table in batches of no more than 100. We may have to reduce this number to be certain there aren't blocking issues on the target side of replication.
Can anyone please provide the script for the same?
Thanks a lot in advance.
August 5, 2010 at 4:41 am
To delete records in small batches like say 100, you can set ROWCOUNT to 100:
SET ROWCOUNT 100
Delete from TableName
The above statements will delete only 100 records from the table;
August 5, 2010 at 1:30 pm
**** There is a duplicate post of this question at http://www.sqlservercentral.com/Forums/Topic964052-391-1 that has more posts in it than this one so after looking here, look there. ****
Something like this may work:
WHILE DATEPART(hour, GETDATE()) < 6
BEGIN
Begin Transaction
DELETE FROM table WHERE table.key_column IN (SELECT TOP(100) T.key_column FROM table AS T ORDER BY T.key_column);
Commit Transaction
END
I choose this method because you aren't guaranteed ordering without providing an order by. If you don't care about the order of deletes then it doesn't really matter. I'd also recommend putting TX Log backups in somewhere if you don't have them happening and the DB is in FULL recovery model.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 5, 2010 at 3:01 pm
FYI - this is a duplicate post to this one - http://www.sqlservercentral.com/Forums/Topic964052-391-1.aspx
August 5, 2010 at 3:22 pm
I like Jack's answer best. SET ROWCOUNT 100 will limit results system wide to 100 rows until it is set back to 0. If you do use that, make sure you set it back ASAP or there will be some angry users calling you to see what is going on.
August 5, 2010 at 5:07 pm
jerry-621596 (8/5/2010)
I like Jack's answer best. SET ROWCOUNT 100 will limit results system wide to 100 rows until it is set back to 0. If you do use that, make sure you set it back ASAP or there will be some angry users calling you to see what is going on.
I'm going to answer here - since the link above says it is forbidden 🙂
Anyways, using SET ROWCOUNT will not work in future releases of SQL Server against DELETE, INSERT and UPDATE statements. You should use TOP instead - as in:
Declare @rowsAffected int = 1;
Set @rowsAffected = 1;
While @rowsAffected > 0
Begin
Delete Top(100)
From dbo.Your_Table;
Set @rowsAffected = @@rowcount;
Checkpoint; --assume simple recovery, if full - change to backup log...
End;
Besides, this is safer than using ROWCOUNT as it only affects the statement being executed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 5, 2010 at 5:09 pm
August 5, 2010 at 7:31 pm
Lynn Pettis (8/5/2010)
I guess I'll post my answer in both.Here is an article you may want to read as well: Deleting Large Number of Records[/url].
heh - I found the other thread after I posted above, saw your article and realized we are basically on the same page. Good article - I'll be recommending it.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 6, 2010 at 2:48 am
Thanks all for your reply.
But i do have quick question, how can i start at 12:00 everynight and finish at 6:00 AM with some delay so it wont overwhelm application
August 6, 2010 at 2:52 am
You can use Job Scheduler of SQL Server to schedule your task at 12:00.
August 6, 2010 at 3:03 am
What if i would like to without scheduling job?
Is there any way i can do that? like using Date function...
August 6, 2010 at 3:12 am
Is there any way to do without Scheduling job?
Like using date function.....
August 6, 2010 at 7:13 am
harry79 (8/6/2010)
Thanks all for your reply.But i do have quick question, how can i start at 12:00 everynight and finish at 6:00 AM with some delay so it wont overwhelm application
As Harry said you'd need to have it scheduled to kick off using SQL Server agent. If you look at the code I provided in my original post you'll see that I have the delete in a WHILE loop. That means once it gets to 6 AM it will stop. Now it may kick off a delete at 5:59am and the delete may take 10 minutes (shouldn't, but it is possible) so you would run just past 6 AM. If you want a delay between deleted you can put WAITFOR after the COMMIT TRANSACTION.
I'm not sure why you wouldn't want to have a job, and I'm not sure how you would do it without a job.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 6, 2010 at 7:43 am
Hi jacob
Thanks for your reply.
It doesn't mean that i dont want job. But we have to delete that records everyday.
And that deletion must be stop at 6:00.
The job will start at 12:00 AM , but doesnt stop at 6:00, so we have to use that kind of logic.
Here is the code that i am going to use.
Please correct me if i am wrong.
SET NOCOUNT ON
Declare @batchsize int
Set @batchsize = 100
while@batchsize > 0 and DATEPART(hour, GETDATE()) < 6
begin
delete top (@batchsize) from
DBA_TEST..OneIndex
set @batchsize = @@rowcount
WAITFOR DELAY '00:00:00.15'
end
August 6, 2010 at 8:37 am
Archive off data to a table you can hammer.
Have a the batch size configurable.
Allow time for have processes to obtain a lock
DECLARE @n Int
DECLARE @ROWCOUNT int
DECLARE @msg nvarchar(500)
SET @n = (SELECT ConfigurationValue FROM Maint.ConfigurationTable WHERE ConfigurationName = 'BatchSize')
SET @n = ISNULL(@n,1000)
WHILE (1=1)
BEGIN
BEGIN TRY
BEGIN TRANSACTION
DELETE TOP(@n) FROM [dbo].
OUTPUT DELETED.COLUMNS INTO [dbo].[ARCHIVE]
WHERE COLUMN IN ('')
SET @ROWCOUNT = @@ROWCOUNT
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
END CATCH
SET @msg = cast(@ROWCOUNT as varchar) + ' Rows deleted'
RAISERROR(@msg,0,1) WITH NOWAIT
WAITFOR DELAY '00:00:02' -- allow other transaction to process
IF @ROWCOUNT = 0
BREAK
ELSE
CONTINUE
END
GO
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply