Posted by Erland Sommarskog on 02/03/06 00:25

rosherman@hotmail.com (rosherman@hotmail.com) writes:
> We are using SQL Server 2000, and one of the tables stores user
> sessions details (each time our users logs into our system we insert a
> new record in the session table, and each time user logs out from our
> system we insert another record in the same table).
> SESSION_ID is the primary key and it is clustered index.
> The system produces 5 million session records/day.
>
> The problem:
>
> Each day we transfer the session data (delta only) to other machine and
> we want to delete bulk of ~5 million sessions. This should happend
> without any interfering of our customers activity ( in the same time,
> we should not block the table - new sessions should be created).
>
> What is the best way to perform such task ?

If I understand this correctly, you want to delete the main bulk of the
five million rows, but keep some of them.

I would consider doing something like:

1) Rename the table.
2) Create a new table with the same schema.
3) Insert the rows you want to keep from the old table to the new table.
4) Drop the old table.

You would need to put 1) and 2) into a transaction. During this
transactions logins would be blocked, but it would be a matter of
centiseconds.

If you can find a method to define a clean cut a head, then you could
consider partitioned views. That is, you would have a set of table
that are united in a view, and a CHECK constraint defining which
intervals that go into which table. Insertions would be into the view.
You would transfer one table a time, and then truncate and finally
redefine it to fit another slot in the future.

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

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

[Back to original message]