deleting 200 million rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

deleting 200 million rows

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-06-27 : 03:47:51

how can i delete 200 million rows in a db set to FULL backup
and i dont want the Log file to grow up to big size?

thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-27 : 04:04:28

If you want to delete all rows in a table, truncate it

Madhivanan

Failing to plan is Planning to fail

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-06-27 : 04:07:55

impossible!
i will lose other 180 million that i have there

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-27 : 04:17:44

delete them in batches of 10000

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-06-27 : 04:25:43

what do u mean by that?
to do something like
delete from xxx where id >100000 and id<101000 and so on?
what the diffrence?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-27 : 04:42:42

your transaction log won't grow like mad

something like
set rowcount 10000
while @@rowcount != 0
begin
delete from myTable where MyCondition
end
set rowcount 0
_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-06-27 : 05:06:01

but in this case i need to do a loop inside a loop or u suggest to to run a job every 5 minutes?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-27 : 05:23:50

why every 5 minutes?
this is new information.

please post your full requirement in detail.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-06-27 : 05:28:17

no 5 minutes was for spliting it to every 5 minutes deleteing 10000 rows
so how do you delete 200 milion rows in your way?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-27 : 05:53:50

exactly as i posted.
there's no 5 minute wait.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-06-27 : 05:59:48

ok so how do i delete 200 million rows?
by :
set rowcount 200000000

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-27 : 06:12:00

no.

please open up BOL = books Online = sql server help and see what set rowcount does.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-06-27 : 06:30:51

quote:
Following INSERT, UPDATE, or DELETE statements, this function returns the number of rows affected by the data modification statement.

that i know
but again how do break into blocks of 10000 each delete?
to make a run evvery 1 minutes of 10000 lines ?or what execlly the idea?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-27 : 06:32:44

Do you know which rows should be deleted?
If so, in spirit's query use it in Where clause

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-06-27 : 06:37:41

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Bulk+Delete+of+Records

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-06-27 : 07:16:03

i want to make
delete from myTable where id<270000000
the loop wont help

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-27 : 07:30:00

the loop wont help???

i give up...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-27 : 08:39:00

quote:
Originally posted by pelegk2

i want to make
delete from myTable where id<270000000
the loop wont help

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

How did you know the loop wont help?
Did you execute the query?
Can you define your conclusion?

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-27 : 08:41:01

quote:
Originally posted by spirit1

the loop wont help???

i give up...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

I think for the first time, I read this reply from you