DELETING 100 million from a table weekly SQl SERVER 2000

DELETING 100 million from a table weekly SQl SERVER 2000
Hi All
We have a table in SQL SERVER 2000 which has about 250 million records
and this will be growing by 100 million every week. At a time the table
should contain just 13 weeks of data. when the 14th week data needs to
be loaded the first week's data has to be deleted.
And this deletes 100 million every week, since the delete is taking lot
of transaction log space the job is not successful.
Can you please help with what are the approaches we can take to fix
this problem?
Performance and transaction log are the issues we are facing. We tried
deletion in steps too but that also is taking time. What are the
different ways we can address this quickly.
Please reply at the earliest.
Thanks
Harish
If you can move up to SQL Server 2005, then you should look at partitioning the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"harish" <harish.prabhala@.gmail.com> wrote in message
news:1131162784.737373.142270@.g43g2000cwa.googlegr oups.com...
> DELETING 100 million from a table weekly SQl SERVER 2000
> Hi All
> We have a table in SQL SERVER 2000 which has about 250 million records
> and this will be growing by 100 million every week. At a time the table
> should contain just 13 weeks of data. when the 14th week data needs to
> be loaded the first week's data has to be deleted.
> And this deletes 100 million every week, since the delete is taking lot
> of transaction log space the job is not successful.
> Can you please help with what are the approaches we can take to fix
> this problem?
> Performance and transaction log are the issues we are facing. We tried
> deletion in steps too but that also is taking time. What are the
> different ways we can address this quickly.
> Please reply at the earliest.
> Thanks
> Harish
>
|||Hey
Thanks. We have an index on four columns in this table. For Ex A, B, C
and D
The delete statement's where clause has the conditions for A, B and C
The delete statement's where clause has the conditions for just A
Which of the two's performance will be faster?
We tried something like this:
SET ROWCOUNT 5000;
WHILE 1 = 1
BEGIN
DELETE FROM T1 WHERE dt < '20030101' -- original delete
IF @.@.rowcount < 5000 BREAK;
END
SET ROWCOUNT 0;
1) Does this setting ROWCOUNT first sort the table and then delete?
2) The above query is executed to delete all records satisfying the
condition in steps of 5000 until the delete is comple.
How can I stop it after one 5000?
|||"harish" <harish.prabhala@.gmail.com> wrote in message
news:1131217180.922372.211460@.g44g2000cwa.googlegr oups.com...
> Thanks. We have an index on four columns in this table. For Ex A, B, C
> and D
> The delete statement's where clause has the conditions for A, B and C
> The delete statement's where clause has the conditions for just A
>
> Which of the two's performance will be faster?
There should be virtually no difference. What might be faster would be
using a predicate that can be satisfied by the clustered index, to avoid
doing two lookups.

> 1) Does this setting ROWCOUNT first sort the table and then delete?
No; it just deletes the first N rows it finds that satisfy the
predicate.

> 2) The above query is executed to delete all records satisfying the
> condition in steps of 5000 until the delete is comple.
> How can I stop it after one 5000?
Remove the loop.
Adam Machanic
Pro SQL Server 2005, available now
www.apress.com/book/bookDisplay.html?bID=457
|||On 5 Nov 2005 10:59:40 -0800, harish wrote:
(snip)
Hi Harish,
I just replied to the same question in another thread.
Could you please ask your questions in JUST ONE place, and in JUST ONE
group? I've seen your messages scattered over several groups, and
several different messages in just this group. Many of them have
attracted replies. It's very hard to keep track of what is going on in
all thesse threads, and it's a waste of other people's time if someone
posts a reply to you that you already had received in another group.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Where's the other thread?
Adam Machanic
Pro SQL Server 2005, available now
www.apress.com/book/bookDisplay.html?bID=457
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:uq5qm1drmjlp5kt0hp0jl3krefsm9qgff8@.4ax.com...
> On 5 Nov 2005 10:59:40 -0800, harish wrote:
> (snip)
> Hi Harish,
> I just replied to the same question in another thread.
> Could you please ask your questions in JUST ONE place, and in JUST ONE
> group? I've seen your messages scattered over several groups, and
> several different messages in just this group. Many of them have
> attracted replies. It's very hard to keep track of what is going on in
> all thesse threads, and it's a waste of other people's time if someone
> posts a reply to you that you already had received in another group.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Sat, 5 Nov 2005 18:08:27 -0500, Adam Machanic wrote:

>Where's the other thread?
Hi Adam,
One in microsoft.public.sqlserver.newusers (that's where I replied to
his questions - but don't bother to look it up, my answers are virtually
identical; to yours).
One in microsoft.public.sqlserver.server.
And no less than THREE in microsoft.public.sqlserver.programming.
If there were more, I didn't see them.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo
This is Harish again
set rowcount 100000
declare @.a int
while 1=1
begin
begin tran del1
delete from dbo.extt_vpm_ldr_stg_2
select @.a = @.@.rowcount
print @.a
commit tran del1
if @.a < 100000 break;
end
TOTAL TABLE SIZE - 650 000 records
I am using the above statement to delete in chunks on 100000.
After every 100000 I have put a COMMIT TRAN. Hence as per the logic it
should delete 100000 commit and delete the next 100000.
And the transaction log is suppose 100MB before the statement is
executed. It is increasing in steps of some 30MB for each delete to
upto some 250MB and then goes back to some 50MB.
What we need is the transaction log must increase for every delete and
drop and again increase and so on.
How can the above statement be modified to take care of this behaviour?
Please reply asap.
Thanks
Harish
|||On 9 Nov 2005 08:10:52 -0800, harish wrote:

>Hi Hugo
>This is Harish again
Hi Harish,
This is my previous reply again
Could you please ask your questions in JUST ONE place, and in JUST ONE
group? I've seen your messages scattered over several groups, and
several different messages in just this group. Many of them have
attracted replies. It's very hard to keep track of what is going on in
all thesse threads, and it's a waste of other people's time if someone
posts a reply to you that you already had received in another group.
BTW, I already saw your question in one of the other groups, and I have
already seen at least one answer (can't recall off the top of my head
who wrote it, though).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)