millions records archiving and delete

[Massimo]

The iussue:

Sql 2K
I have to keep in the database the data from the last 3 months.
Every day I have to load 2 millions records in the database.
So every day I have to export (in an other database as historical data
container) and delete the 2 millions records inserted 3 month + one day ago.

The main problem is that delete operation take a while...involving
transaction log.

The question are:
1) How can I improve this operation (export/delete)
2) If we decide to migrate to SQL 2005, may we use some feature, as
"partitioning" to resolve the problems ? In oracle I can use the "truncate
partition" statement, but in sql 2005, I'm reading, it cant be done.
This becouse we can think to create a partition on the last three mounts to
split data. The partitioning function can be dinamic or containing a
function that says "last 3 months ?" I dont think so.

May you help us
thank you

Mastino

[Erland Sommarskog]

Massimo (ma*****@hotmail.it) writes:

Sql 2K
I have to keep in the database the data from the last 3 months.
Every day I have to load 2 millions records in the database. So every
day I have to export (in an other database as historical data container)
and delete the 2 millions records inserted 3 month + one day ago.

The main problem is that delete operation take a while...involving
transaction log.

The question are:
1) How can I improve this operation (export/delete)
2) If we decide to migrate to SQL 2005, may we use some feature, as
"partitioning" to resolve the problems ? In oracle I can use the
"truncate partition" statement, but in sql 2005, I'm reading, it cant be
done. This becouse we can think to create a partition on the last three
mounts to split data. The partitioning function can be dinamic or
containing a function that says "last 3 months ?" I dont think so.

Permit me to start with SQL 2005. There you have partitioned tables,
and in a case like yours you would set up the table with let's say
four partitions, with the month as the partitioning column. To delete
old rows, you would simply take that table out of the partition
table, and then drop table that table. You in the same manner, shift
in a new table for the next month. Here I said month, but you have one
partition per day, and have 90 partitions if you like - whether this
is a good idea I don't know.

Note that partitioned tables are only available in the Enterprise
(and Developer) Edition of SQL 2005.

In SQL 2005, you would use partitioned views (and here 90 paritions
would definitely go beyond what is manageable). One table per month
and then they are united in a view with a UNION ALL statement. At
a new month you would run a job that dropped the table from four
months back, and create a new table. Notice that you can load directly
to the new, and data should turn in the right place.

See also Stefan Delmarco's article on partitioned views:
http://www.fotia.co.uk/fotia/FA.02.S...dViews.01.aspx
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Mastino]

First, thank you for the answer, Erland, it's not the first time you help me
!

Permit me to start with SQL 2005. There you have partitioned tables,
and in a case like yours you would set up the table with let's say
four partitions, with the month as the partitioning column. To delete
old rows, you would simply take that table out of the partition
table, and then drop table that table. You in the same manner, shift
in a new table for the next month. Here I said month, but you have one
partition per day, and have 90 partitions if you like - whether this
is a good idea I don't know.

Reading and reading over the internet, I found that I can transfer the data
to be dropped with a:

ALTER TABLE SWITCH...

and I can also make partition function dynamic:

http://msdn2.microsoft.com/en-us/library/aa964122.aspx

now I'm studying hard for the solution, the problems to resolve are many.

>
Note that partitioned tables are only available in the Enterprise
(and Developer) Edition of SQL 2005.

In SQL 2005, you would use partitioned views (and here 90 paritions
would definitely go beyond what is manageable). One table per month
and then they are united in a view with a UNION ALL statement. At
a new month you would run a job that dropped the table from four
months back, and create a new table. Notice that you can load directly
to the new, and data should turn in the right place.

See also Stefan Delmarco's article on partitioned views:
http://www.fotia.co.uk/fotia/FA.02.S...dViews.01.aspx

I do not want, and I cannot use partitioned wiews, I have to delete what we
do not need any more.

>

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thank you

Massimo / Mastino

[Erland Sommarskog]

Mastino (ma*****@hotmail.it) writes:

I do not want, and I cannot use partitioned wiews, I have to delete what
we do not need any more.

Why would partitioned views prevent that? When it's time to delete old data,
you first redefine the view, so that the tables to be dropped are not
in the view any more. Then deleting is just dropping the table.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Massimo-Mastino]

I will evaluate this way too, but we have to work with millions records
tables.
Thanx

"Erland Sommarskog" <es****@sommarskog.seha scritto nel messaggio
news:Xn**********************@127.0.0.1...

Mastino (ma*****@hotmail.it) writes:

I do not want, and I cannot use partitioned wiews, I have to delete what
we do not need any more.

Why would partitioned views prevent that? When it's time to delete old

data,

you first redefine the view, so that the tables to be dropped are not
in the view any more. Then deleting is just dropping the table.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[othellomy]

On Feb 28, 12:20 am, "Massimo" <[e-mail]:

The iussue:

Sql 2K
I have to keep in the database the data from the last 3 months.
Every day I have to load 2 millions records in the database.
So every day I have to export (in an other database as historical data
container) and delete the 2 millions records inserted 3 month + one day ago.

The main problem is that delete operation take a while...involving
transaction log.

The question are:
1) How can I improve this operation (export/delete)
2) If we decide to migrate to SQL 2005, may we use some feature, as
"partitioning" to resolve the problems ? In oracle I can use the "truncate
partition" statement, but in sql 2005, I'm reading, it cant be done.
This becouse we can think to create a partition on the last three mounts to
split data. The partitioning function can be dinamic or containing a
function that says "last 3 months ?" I dont think so.

May you help us
thank you

Mastino

Just out of curiosity, do you have to log the delete operation? You
can truncate the tables but that is not logged.