Archiving Past Day's Data's

Question

Post reply

Archiving Past Day's Data's

sqlusers

SSCrazy

Points: 2463
More actions
October 12, 2012 at 3:33 am

#269497

Hi Folks,
We have a DWH database in which one of a table has more than 5 Crore of records. In that table, nearly 15 Laks records are getting loaded on daily basis.
We want to Archive [Not Deleting the real data's] some past days records from this table. For example, we need Archive July, August, September month data's.
Can i know which is the effective way to do this?
Thanks in Advance!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

durai nagarajan SSCoach Points: 15778 More actions · Answer 1

daily data of 15 lakhs has total of 5 crore means average of 30 -35 days of data only right.

do you have any keys / indexes in the table.

try moving old data to different DB and link it and use.

Regards
Durai Nagarajan

VastSQL SSC-Dedicated Points: 36096 More actions · Answer 2

you can move old data to another table on same db. Create some job to achieve the same.

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

FYI: http://en.wikipedia.org/wiki/South_Asian_numbering_system

Edit: fixed link

durai nagarajan SSCoach Points: 15778 More actions · Answer 4

homebrew01 (10/12/2012)

FYI: http://en.wikipedia.org/wiki/South_Asian_numbering_system%5B/quote%5D

what is this?

Regards
Durai Nagarajan

Jeff Moden SSC Guru Points: 1004301 More actions · Answer 5

sqlusers (10/12/2012)
Hi Folks,
We have a DWH database in which one of a table has more than 5 Crore of records. In that table, nearly 15 Laks records are getting loaded on daily basis.
We want to Archive [Not Deleting the real data's] some past days records from this table. For example, we need Archive July, August, September month data's.
Can i know which is the effective way to do this?
Thanks in Advance!

Enterprise or Standard Edition?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kevaburg SSCoach Points: 18131 More actions · Answer 6

Hi,

would this not be the perfect opportunity to partition your database. Select the criteria on which the partition would be created (ie: by date) and use an automated job to move the redundant data to another partition\storage device\table on a pretermined schedule.

At least that is what I would do......

Regards

Kev

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

durai nagarajan (10/12/2012)

homebrew01 (10/12/2012)
FYI: http://en.wikipedia.org/wiki/South_Asian_numbering_system%5B/quote%5D
what is this?

A link to a wiki page to help explain what a crore and a lak is. There are many of us that have no idea what these terms mean.

Let's make it easier for others to us this link:

http://en.wikipedia.org/wiki/South_Asian_numbering_system

Jeff Moden SSC Guru Points: 1004301 More actions · Answer 8

kevaburg (10/12/2012)
Hi,
would this not be the perfect opportunity to partition your database. Select the criteria on which the partition would be created (ie: by date) and use an automated job to move the redundant data to another partition\storage device\table on a pretermined schedule.
At least that is what I would do......
Regards
Kev

I was thinking the same thing when I asked which Edition of SQL Server they have. If Enterprise then Table Partion. If Standard Edition then Partioned View.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

durai nagarajan SSCoach Points: 15778 More actions · Answer 9

Lynn Pettis (10/12/2012)

durai nagarajan (10/12/2012)
homebrew01 (10/12/2012)
FYI: http://en.wikipedia.org/wiki/South_Asian_numbering_system%5B/quote%5D
what is this?
A link to a wiki page to help explain what a crore and a lak is. There are many of us that have no idea what these terms mean.
Let's make it easier for others to us this link:
http://en.wikipedia.org/wiki/South_Asian_numbering_system

oh sorry i thought he wrongly posted it..

Regards
Durai Nagarajan

sqlusers SSCrazy Points: 2463 More actions · Answer 10

Sorry for the delay response. 🙂

Yes, we have indexes and using standard edition. Seems different DB will not work out since permission issue.

Will try to automate as per the suggestions.

Thanks!

Jeff Moden SSC Guru Points: 1004301 More actions · Answer 11

sqlusers (10/12/2012)
Sorry for the delay response. 🙂
Seems different DB will not work out since permission issue.
Thanks!

Then archiving probably isn't going to do anything for you. You just need to index correctly and then write effecient queries against it. To make index maintenance easier, consider using a Partitioned View (since you have standard edition).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)