large data management

Hi all, I have a problem with sqlserver 2000 and large data management.
I have a database with a large tables.
Every table has a continuative input data flow
every morning a job delete old records from the tables (delete one most
old day)
but sometimes I belive that this operation is blocking for the table and
the continuative data flow on that table fails for timeout.
Infact the delete operation is most espesive in time resource. (4-5 hour
for every table)
I must say that the table has an index on datetime field.
I can't use trucate table because I can't delete all records.
In delete table can't use the option "with no lock"
perhaps I must to recalc datatime index on a table before delete? any
other idea?
tnx in advance

Re: large data management

Enorme Vigenti (LSimon5@libero.it) writes:
> Hi all, I have a problem with sqlserver 2000 and large data management.
> I have a database with a large tables.
> Every table has a continuative input data flow
> every morning a job delete old records from the tables (delete one most
> old day)
> but sometimes I belive that this operation is blocking for the table and
> the continuative data flow on that table fails for timeout.
> Infact the delete operation is most espesive in time resource. (4-5 hour
> for every table)
> I must say that the table has an index on datetime field.
> I can't use trucate table because I can't delete all records.
> In delete table can't use the option "with no lock"
> perhaps I must to recalc datatime index on a table before delete? any
> other idea?

Had you been on SQL 2005, you could have used partitioned tables. This
requires Enterprise Edition, I should hasten to add. With partitioned
table, deleting old data can be as smooth as a metadata operation,
if you set it up correctly.

On SQL 2000 (and SQL 2005 Standard and lower editions) you can use
partitioned views. This requires some more work, but is still doable.
And again the delete can be as simple as dropping the table.

SQL Server MVP Stefan Delmarco has a good article on partitoned views
on http://www.fotia.co.uk/fotia/FA.02.Sql2KPartitionedViews.01. aspx.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: large data management

Erland Sommarskog ha scritto:

> Had you been on SQL 2005, you could have used partitioned tables. This
> requires Enterprise Edition, I should hasten to add. With partitioned
> table, deleting old data can be as smooth as a metadata operation,
> if you set it up correctly.
>
> On SQL 2000 (and SQL 2005 Standard and lower editions) you can use
> partitioned views. This requires some more work, but is still doable.
> And again the delete can be as simple as dropping the table.
>
> SQL Server MVP Stefan Delmarco has a good article on partitoned views
> on http://www.fotia.co.uk/fotia/FA.02.Sql2KPartitionedViews.01. aspx.
>
Many thanks :-)
Very good!