How to delete huge amount of data

Question

Post reply

How to delete huge amount of data

tikkani.praveen

Old Hand

Points: 353
More actions
August 14, 2009 at 11:06 am

#385188

Hi all,
I have a table which contains 103 crore records, month wise.now i have to delete past few month records. I can say each month contains 6 crore records.
When i am trying to delete by filter on month, the process is getting slow and logs are full.
Can anybody tell me an alternate way of doing it in quicker time.
Thanks,
Praveen

Viewing 6 posts - 1 through 5 (of 5 total)

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

Plase excuse my ignorance, but how much is a "crore"?

does one "crore" represent ten million (10,000,000) ?

gah

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

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

Here is some code to help you get started. Hopefully the article I had published on sswug.org will be republished here on SSC sometime in September.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

gah (8/14/2009)
Plase excuse my ignorance, but how much is a "crore"?
does one "crore" represent ten million (10,000,000) ?
gah

From WikiPedia.com...

A crore (Hindi: ?????) (often abbreviated cr) is a unit in the Indian numbering system equal to ten million (10,000,000; 107), or 100 lakh. It is widely used in Bangladesh, India, Maldives, Nepal, and Pakistan. It was 500,000 in the now-obsolete Persian number system.

--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)

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

Jeff Moden (8/14/2009)
gah (8/14/2009)
Plase excuse my ignorance, but how much is a "crore"?
does one "crore" represent ten million (10,000,000) ?
gah
From WikiPedia.com...
A crore (Hindi: ?????) (often abbreviated cr) is a unit in the Indian numbering system equal to ten million (10,000,000; 107), or 100 lakh. It is widely used in Bangladesh, India, Maldives, Nepal, and Pakistan. It was 500,000 in the now-obsolete Persian number system.

Okay, 103 crore records = 1,030,000,000 records. That a heck of al ot of records! :w00t:

John Rowan SSC Guru Points: 56440 More actions · Answer 5

Enter SQL Server 2005 table partitioning. I would recommend looking into partitioning your table by date. Your delete/purge process will become a metadata switch instead of a traditional delete. Here's a great read by Kimberly Tripp on the topic:

http://msdn.microsoft.com/en-us/library/ms345146(SQL.90).aspx

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden