Prev: Removing duplicates in multiple tables
Next: Pivot in a Join
From: SqlBeginner on 17 Jun 2010 20:51
Have a table which sales data from the year 2000. The Database doesn't seem
to have any archival mechanism as of now. Now the mgmt feels they don't need
data beyond last 'n' years.

So delete all those data which is beyond 'n' years is a huge process as i
feel it would fill up the transaction log. What would be the best way to
approach this problem.

Regards
Pradeep
From: Jeroen Mostert on 17 Jun 2010 23:54
On 2010-06-18 2:51, SqlBeginner wrote:
> Have a table which sales data from the year 2000. The Database doesn't seem
> to have any archival mechanism as of now. Now the mgmt feels they don't need
> data beyond last 'n' years.
>
You may want to look at OLAP solutions. Migrating the data to a database
specially designed to hold large amounts of data and aggregate totals may
open up opportunities. Data is almost always more valuable than storage
(less-frequently used data should simply be moved to cheaper storage if you
can't afford to make the existing storage bigger).

Failing that, just moving the data to a new database and backing that up
somewhere so you can always retrieve the data on an unexpected occasion (or
build the aforementioned OLAP solution) still makes more sense than throwing
it away.

> So delete all those data which is beyond 'n' years is a huge process as i
> feel it would fill up the transaction log. What would be the best way to
> approach this problem.
>
Batch-wise deleting, i.e.

WHILE EXISTS (SELECT * FROM table WHERE ...) DELETE TOP(1000) FROM TABLE
WHERE ...

or whatever number makes sense given your table size and transaction log
size. This deletes records in nondeterministic order (not necessarily oldest
or newest first).

From a pure performance point of view, if you have much more data to delete
than to retain, it makes more sense to create a new table containing the
data you want (with INSERT INTO), drop the old table and rename the new one.
However, this is trickier because things like indexes and constraints need
to be migrated as well, and if records are continuously added to your table
you need to make the switch more-or-less atomic. These problems have
solutions, but deleting from the original table is simpler.

That said, deleting from the original table may not make your database any
smaller, because the storage it took up can probably not be reclaimed
without shrinking the database. Don't do this if you can possibly help it,
as it fragments the data, which is bad for performance. At least clearing
the table will mean the database won't grow for a while, and backups will
still be smaller.

--
J.
From: Uri Dimant on 20 Jun 2010 04:09
insert into Archive..dbArchive
select getdate(),d.*
from (delete top (10000)
from Dbname..tblname
output deleted.*) d
go



"SqlBeginner" <SqlBeginner(a)discussions.microsoft.com> wrote in message
news:37A9F833-893B-4783-A436-10F284CE92A1(a)microsoft.com...
> Have a table which sales data from the year 2000. The Database doesn't
> seem
> to have any archival mechanism as of now. Now the mgmt feels they don't
> need
> data beyond last 'n' years.
>
> So delete all those data which is beyond 'n' years is a huge process as i
> feel it would fill up the transaction log. What would be the best way to
> approach this problem.
>
> Regards
> Pradeep


 | 
Pages: 1
Prev: Removing duplicates in multiple tables
Next: Pivot in a Join