deleting only 1000 rows at the time

Hi!
I have a huge table with about 40 mil of rows. I just discovered
that 10 mil can be safely deleted however, I do not want to delete all
in one shot. I would like to delete 10000 rows at the time.
This will start removing all 10 millions:
delete from apps where code = 'I'
how can I delete 10000 at the time?
Thank you,
T.On Sep 7, 10:42 am, tolcis <nytolly...@.gmail.com> wrote:
> Hi!
> I have a huge table with about 40 mil of rows. I just discovered
> that 10 mil can be safely deleted however, I do not want to delete all
> in one shot. I would like to delete 10000 rows at the time.
> This will start removing all 10 millions:
> delete from apps where code = 'I'
> how can I delete 10000 at the time?
> Thank you,
> T.
DELETE TOP(100) FROM ...|||What version of SQL Server are you running? With SQL Server 2005 the
TOP keyword is supported for DELETE, so it would look like:
DELETE TOP 100 FROM apps WHERE code = 'I'
SQL Server 2000 does not support TOP directly as part of DELETE so it
is a bit more complicated.
DELETE apps
FROM (SELECT TOP 1000 * FROM apps) as X
WHERE apps.appsKey = X.appsKey
Roy Harvey
Beacon Falls, CT
On Fri, 07 Sep 2007 08:42:54 -0700, tolcis <nytollydba@.gmail.com>
wrote:
>Hi!
>I have a huge table with about 40 mil of rows. I just discovered
>that 10 mil can be safely deleted however, I do not want to delete all
>in one shot. I would like to delete 10000 rows at the time.
>This will start removing all 10 millions:
>delete from apps where code = 'I'
>how can I delete 10000 at the time?
>Thank you,
>T.|||tolcis,
It depends on your SQL Server version, but this should work in 2000 and
2005.
DECLARE @.HowMany INT
SET ROWCOUNT 10000
SET @.HowMany = 1
WHILE @.HowMany > 0
BEGIN
delete from apps where code = 'I'
SELECT @.HowMany = @.@.Rowcount
END
SET ROWCOUNT 0
RLF
"tolcis" <nytollydba@.gmail.com> wrote in message
news:1189179774.558864.201630@.o80g2000hse.googlegroups.com...
> Hi!
> I have a huge table with about 40 mil of rows. I just discovered
> that 10 mil can be safely deleted however, I do not want to delete all
> in one shot. I would like to delete 10000 rows at the time.
> This will start removing all 10 millions:
> delete from apps where code = 'I'
> how can I delete 10000 at the time?
> Thank you,
> T.
>|||After playing a bit, seems that a temp table reduce the system impact. On
the other hand, I have been logging in on Sundays to reduce my behemoth 120
million records... have it down to about 90 million at this time. I use the
wait for delay so that the system can move away from the big table to perform
other IO. The wait for delay can be any size. I chose five seconds below.
create table #deletedkeys(gianttablendx bigint)
insert into #deletedkeys(gianttablendx )
select top 1000 raw_event_key from gianttable where gianttablendx < 122786624
delete from bill_raw_transactions where gianttablendx in (select
gianttablendx from #deletedkeys)
truncate table #deletedkeys
WAITFOR DELAY '00:00:05'
GO
insert into #deletedkeys(gianttablendx )
select top 1000 raw_event_key from gianttable where gianttablendx < 122786624
delete from bill_raw_transactions where gianttablendx in (select
gianttablendx from #deletedkeys)
truncate table #deletedkeys
WAITFOR DELAY '00:00:05'
GO
insert into #deletedkeys(gianttablendx )
select top 1000 raw_event_key from gianttable where gianttablendx < 122786624
delete from bill_raw_transactions where gianttablendx in (select
gianttablendx from #deletedkeys)
truncate table #deletedkeys
WAITFOR DELAY '00:00:05'
GO
drop table #deletedkeys
--
Regards,
Jamie
"tolcis" wrote:
> Hi!
> I have a huge table with about 40 mil of rows. I just discovered
> that 10 mil can be safely deleted however, I do not want to delete all
> in one shot. I would like to delete 10000 rows at the time.
> This will start removing all 10 millions:
> delete from apps where code = 'I'
> how can I delete 10000 at the time?
> Thank you,
> T.
>

Deleting Model, MSDBData, Master Databases

Can I safely remove the model, msdbdata and/or the master
databases from SQL2000 (they may actually have been from
SQL7) without affecting the program operation and
population? I am incredibly low on storage (that is whole
other subject) and have already removed the northwind
database. Thanks...
hi carl,
These are system database, do not touch them.
Delete the pubs and northwind databases, they are sample databases provided by SQL Server.
Dont touch model, master, tempdb or msdb.
Vishal Parkar
vgparkar@.yahoo.co.in
sql

Deleting Model, MSDBData, Master Databases

Can I safely remove the model, msdbdata and/or the master
databases from SQL2000 (they may actually have been from
SQL7) without affecting the program operation and
population? I am incredibly low on storage (that is whole
other subject) and have already removed the northwind
database. Thanks...No... Unlike Northwind and Pubs, the three you mentioned are quite
important.
"Carl" <cgrau@.lakelandbank.com> wrote in message
news:19db501c41cd6$c2bfdf30$a101280a@.phx
.gbl...
> Can I safely remove the model, msdbdata and/or the master
> databases from SQL2000 (they may actually have been from
> SQL7) without affecting the program operation and
> population? I am incredibly low on storage (that is whole
> other subject) and have already removed the northwind
> database. Thanks...

Deleting Model, MSDBData, Master Databases

Can I safely remove the model, msdbdata and/or the master
databases from SQL2000 (they may actually have been from
SQL7) without affecting the program operation and
population? I am incredibly low on storage (that is whole
other subject) and have already removed the northwind
database. Thanks...
No... Unlike Northwind and Pubs, the three you mentioned are quite
important.
"Carl" <cgrau@.lakelandbank.com> wrote in message
news:19db501c41cd6$c2bfdf30$a101280a@.phx.gbl...
> Can I safely remove the model, msdbdata and/or the master
> databases from SQL2000 (they may actually have been from
> SQL7) without affecting the program operation and
> population? I am incredibly low on storage (that is whole
> other subject) and have already removed the northwind
> database. Thanks...