Re: DELETING 100 million from a table weekly SQl SERVER 2000

Tech-Archive recommends: Fix windows errors by optimizing your registry

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@xxxxxxxxx> wrote in message news:1131162784.737373.142270@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 
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


.

Relevant Pages

  • Re: Pointy Headed Boss
    ... The log only contains the effect of the modification statements. ... This will be logged as 1000 delete log records in the transaction log. ... Tibor Karaszi, SQL Server MVP ... > replay the trace at a remote site. ...
    (microsoft.public.sqlserver.server)
  • DELETING 100 million from a table weekly SQl SERVER 2000
    ... We have a table in SQL SERVER 2000 which has about 250 million records ... and this will be growing by 100 million every week. ... Performance and transaction log are the issues we are facing. ...
    (comp.databases)
  • Re: TLog Space
    ... Tibor Karaszi, SQL Server MVP ... "Niles" wrote in message ... backing up the database or the transaction log" but it doesn't take. ...
    (microsoft.public.sqlserver.server)
  • Re: Transaction log keeps growing
    ... transaction log keeps growing over time. ... We have been periodically shrinking the database. ... I would imagine that a transaction log can be ...
    (comp.databases.ms-sqlserver)
  • Re: Importing large file speeds up over time
    ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... > The transaction log is currently 8356 MB. ... >> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... >> www.SQLDTS.com - The site for all your DTS needs. ...
    (microsoft.public.sqlserver.dts)