|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
DELETING 100 million from a table weekly SQl SERVER 2000Hi 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.
-- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "harish" <harish.prabh***@gmail.com> wrote in message news:1131162784.737373.142270@g43g2000cwa.googlegroups.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.prabh***@gmail.com> wrote in message There should be virtually no difference. What might be faster would be news:1131217180.922372.211460@g44g2000cwa.googlegroups.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? 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 Remove the loop.> condition in steps of 5000 until the delete is comple. > How can I stop it after one 5000? 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?
Show quote "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 Harish,>Hi Hugo > >This is Harish again 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)  |
|||||||||||||||||||||||
Other interesting topics