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 log file in sql 2000 database

Hi ,
I have a huge sql server 2000 database. Everyday I am importing some
records into it. The .mdf file size is around 20 GB. Surprisingly the
.ldf file size is increasing rapidly and it has reached 50 GB.
I want to import lot more records and there is a possibility that I
may run out of disk space.
Please let me know if I can delete this log file. I can always detach
the database -delete the log file and reattach the database again. But
the question is- Is it safe to do ? Also what is the possibility of
database not getting attached properly and data loss ? any guesses.
Thanks in advance,
Vardhan.Vardahan
Perfom
BACKUP LOG .... WITH NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
are synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Vardhan" <cybage_vardhan@.yahoo.com> wrote in message
news:868ed17f.0401210146.7771e293@.posting.google.com...
> Hi ,
> I have a huge sql server 2000 database. Everyday I am importing some
> records into it. The .mdf file size is around 20 GB. Surprisingly the
> .ldf file size is increasing rapidly and it has reached 50 GB.
> I want to import lot more records and there is a possibility that I
> may run out of disk space.
> Please let me know if I can delete this log file. I can always detach
> the database -delete the log file and reattach the database again. But
> the question is- Is it safe to do ? Also what is the possibility of
> database not getting attached properly and data loss ? any guesses.
> Thanks in advance,
> Vardhan.|||Deleting your log file will kill your database, so its
probably not a good thing to do ;)
Instead either shrink it, or change the SQL so it
immeditatly commits to disk,
J
>--Original Message--
>Hi ,
>I have a huge sql server 2000 database. Everyday I am
importing some
>records into it. The .mdf file size is around 20 GB.
Surprisingly the
>..ldf file size is increasing rapidly and it has reached
50 GB.
>I want to import lot more records and there is a
possibility that I
>may run out of disk space.
>Please let me know if I can delete this log file. I can
always detach
>the database -delete the log file and reattach the
database again. But
>the question is- Is it safe to do ? Also what is the
possibility of
>database not getting attached properly and data loss ?
any guesses.
>Thanks in advance,
>Vardhan.
>.
>|||Does backing up the DB, & then shrinking it not reduce the size?
If it doesnt, this implies you have long-running transactions which might be
stopping the log from being truncated.
Also, have you considered switching to Bulk-Logged recovery model? If most
of this inflation occurs because of the records you are importing, this
might help reduce the log size...
This link might help:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_4l83.asp
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||doesn't work, I also tried backup log files then dbcc shrinkfile (logfile)
this also didn't work. manually deleting the file will be mow much risky ?
thanks in advance
Vardhan.
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:<e2OvHaA4DHA.3416@.tk2msftngp13.phx.gbl>...
> Vardahan
> Perfom
> BACKUP LOG .... WITH NO_LOG | TRUNCATE_ONLY
> Removes the inactive part of the log without making a backup copy of it and
> truncates the log. This option frees space. Specifying a backup device is
> unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
> are synonyms.
> After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
> recorded in the log are not recoverable. For recovery purposes, immediately
> execute BACKUP DATABASE.
> "Vardhan" <cybage_vardhan@.yahoo.com> wrote in message
> news:868ed17f.0401210146.7771e293@.posting.google.com...
> > Hi ,
> > I have a huge sql server 2000 database. Everyday I am importing some
> > records into it. The .mdf file size is around 20 GB. Surprisingly the
> > .ldf file size is increasing rapidly and it has reached 50 GB.
> >
> > I want to import lot more records and there is a possibility that I
> > may run out of disk space.
> >
> > Please let me know if I can delete this log file. I can always detach
> > the database -delete the log file and reattach the database again. But
> > the question is- Is it safe to do ? Also what is the possibility of
> > database not getting attached properly and data loss ? any guesses.
> >
> > Thanks in advance,
> > Vardhan.

Deleting log file in sql 2000 database

Hi ,
I have a huge sql server 2000 database. Everyday I am importing some
records into it. The .mdf file size is around 20 GB. Surprisingly the
.ldf file size is increasing rapidly and it has reached 50 GB.
I want to import lot more records and there is a possibility that I
may run out of disk space.
Please let me know if I can delete this log file. I can always detach
the database -delete the log file and reattach the database again. But
the question is- Is it safe to do ? Also what is the possibility of
database not getting attached properly and data loss ? any guesses.
Thanks in advance,
Vardhan.Vardahan
Perfom
BACKUP LOG .... WITH NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
are synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Vardhan" <cybage_vardhan@.yahoo.com> wrote in message
news:868ed17f.0401210146.7771e293@.posting.google.com...

quote:

---

> Hi ,
> I have a huge sql server 2000 database. Everyday I am importing some
> records into it. The .mdf file size is around 20 GB. Surprisingly the
> .ldf file size is increasing rapidly and it has reached 50 GB.
> I want to import lot more records and there is a possibility that I
> may run out of disk space.
> Please let me know if I can delete this log file. I can always detach
> the database -delete the log file and reattach the database again. But
> the question is- Is it safe to do ? Also what is the possibility of
> database not getting attached properly and data loss ? any guesses.
> Thanks in advance,
> Vardhan.

---

|||Does backing up the DB, & then shrinking it not reduce the size?
If it doesnt, this implies you have long-running transactions which might be
stopping the log from being truncated.
Also, have you considered switching to Bulk-Logged recovery model? If most
of this inflation occurs because of the records you are importing, this
might help reduce the log size...
This link might help:
http://msdn.microsoft.com/library/d... />
t_4l83.asp
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||doesn't work, I also tried backup log files then dbcc shrinkfile (logfile)
this also didn't work. manually deleting the file will be mow much risky ?
thanks in advance
Vardhan.
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:<e2OvHaA4DHA.3416@.tk2msftngp13.phx.gbl>...[Q
UOTE]
> Vardahan
> Perfom
> BACKUP LOG .... WITH NO_LOG | TRUNCATE_ONLY
> Removes the inactive part of the log without making a backup copy of it an
d
> truncates the log. This option frees space. Specifying a backup device is
> unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
> are synonyms.
> After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
> recorded in the log are not recoverable. For recovery purposes, immediatel
y
> execute BACKUP DATABASE.
> "Vardhan" <cybage_vardhan@.yahoo.com> wrote in message
> news:868ed17f.0401210146.7771e293@.posting.google.com...

delete multiple tables

i have a inherited adatabase application that creates a table everyday.
because a table is created evryday the database is becoming huge. after muc
h
deliberation i have decided that i only need to keep 30 days of tables. how
can i delete all tables older than 30 days inside the database? i am fairly
new to sql and i have not been able to figure out how to delete multiple
tables based on creation date.
ronnieHi Ronnie
Ur idea in deleting old tables looks good but never try to delete a table
based on the date they were created.
This might also delete your production tables in the database which are
important
create your tables for ex: ForDelete_<Table_name>_<date>
and now u can write a stored procedure to loop arround and delete these kind
of tables.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Ronnie" wrote:

> i have a inherited adatabase application that creates a table everyday.
> because a table is created evryday the database is becoming huge. after m
uch
> deliberation i have decided that i only need to keep 30 days of tables. h
ow
> can i delete all tables older than 30 days inside the database? i am fair
ly
> new to sql and i have not been able to figure out how to delete multiple
> tables based on creation date.
> ronnie|||See if this helps, but first be sure to have a backup of the db before using
it in production.
use your_db
go
declare @.sql nvarchar(4000)
declare @.ts sysname
declare @.tn sysname
declare @.cd datetime
declare tables_older_than_30_days cursor local fast_forward
for
select
user_name(uid) as table_schema,
[name] as table_name,
crdate
from
sysobjects
where
xtype = 'U'
and objectproperty([id], 'IsMSShipped') = 0
and datediff(day, crdate, getdate()) > 30
open tables_older_than_30_days
while 1 = 1
begin
fetch next from tables_older_than_30_days into @.ts, @.tn, @.cd
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'drop table ' + quotename(@.ts) + N'.' + quotename(@.tn)
print @.sql
-- uncomment this line to drop the table
-- exec sp_executesql @.sql
end
close tables_older_than_30_days
deallocate tables_older_than_30_days
go
AMB
"Ronnie" wrote:

> i have a inherited adatabase application that creates a table everyday.
> because a table is created evryday the database is becoming huge. after m
uch
> deliberation i have decided that i only need to keep 30 days of tables. h
ow
> can i delete all tables older than 30 days inside the database? i am fair
ly
> new to sql and i have not been able to figure out how to delete multiple
> tables based on creation date.
> ronnie|||Ronny
Wow , it looks to me you have a poor designed database
Why do you/someone else need to create a table every day?
In sysobjects system table there is a cdate column .
"Ronnie" <Ronnie @.discussions.microsoft.com> wrote in message
news:BC19E550-1D16-4B6D-952A-31CA103B2FE3@.microsoft.com...
> i have a inherited adatabase application that creates a table everyday.
> because a table is created evryday the database is becoming huge. after
much
> deliberation i have decided that i only need to keep 30 days of tables.
how
> can i delete all tables older than 30 days inside the database? i am
fairly
> new to sql and i have not been able to figure out how to delete multiple
> tables based on creation date.
> ronnie|||the database is for an automated vehicle location tacking. each day becomes
its own table. the table is populated with gps points for multiple vehicles
.
i am not sure why they set it up this way. i have not given it a lot of
thought yet. first i want to clean it up then look at redsigning it. thank
s
for the tip.
ronnie
"Uri Dimant" wrote:

> Ronny
> Wow , it looks to me you have a poor designed database
> Why do you/someone else need to create a table every day?
> In sysobjects system table there is a cdate column .
>
>
> "Ronnie" <Ronnie @.discussions.microsoft.com> wrote in message
> news:BC19E550-1D16-4B6D-952A-31CA103B2FE3@.microsoft.com...
> much
> how
> fairly
>
>