<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Administering
»
Deleting records from a huge table
22 posts, Page 1 of 3
1
2
3
»
»»
Deleting records from a huge table
Rate Topic
Display Mode
Topic Options
Author
Message
Sanz
Sanz
Posted Monday, February 08, 2010 7:09 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:11 PM
Points: 164,
Visits: 702
I have a huge table in my database. I need to delete records from this table. There are no indexes on this table. If I create an index on one of the columns the deletion process will run faster but the index creation will take a long time.
I need to know the best method to delete rows from this table.
Thanx in Advance !
Sanz
Post #861638
george sibbald
george sibbald
Posted Monday, February 08, 2010 7:27 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 4:02 PM
Points: 5,269,
Visits: 11,203
If you have enterprise edition maybe partitioning the table will work for you, else you probably want to go with a crawler delete to avoid blowing the log and reduce contention.
If you are deleting the majority of the table could be faster to insert data you want to keep into another table, drop original, then rename new table.
We really need to know the table schema, a bit of example data and the criteria you wish to delete by.
---------------------------------------------------------------------
Post #861656
maechismo_8514
maechismo_8514
Posted Monday, February 08, 2010 10:45 AM
Ten Centuries
Group: General Forum Members
Last Login: Friday, August 24, 2012 8:11 AM
Points: 1,097,
Visits: 2,157
Sanz (2/8/2010)
I have a huge table in my database. I need to delete records from this table. There are no indexes on this table. If I create an index on one of the columns the deletion process will run faster but the index creation will take a long time.
I need to know the best method to delete rows from this table.
Thanx in Advance !
How many records approx. How huge the table you are talking about? Delete chunks of data.
Post #861883
Sanz
Sanz
Posted Monday, February 08, 2010 10:54 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:11 PM
Points: 164,
Visits: 702
The table is 135 GB in size. There are 5 columns all of which have datatype int and varchar(15).
I would keep George's suggestion as an option (Inserting into a new table) as I will be deleting nearly 50% of the data.
Any better idea ??
Sanz
Post #861891
Amit Singh
Amit Singh
Posted Monday, February 08, 2010 12:27 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Sunday, May 05, 2013 3:41 PM
Points: 110,
Visits: 368
copying to another table would require another 135 GB on the disk ..... do we have that much space ...
you could try a BCP with queryout to output to a file (smaller in size, faster as well) with the data that you eventually want to keep.
then you truncate the table
then you BCP in the file from step 1
just some thoughts....
-------------------------------------------------
-Amit
Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !!
- desparately trying to fish
Post #861966
bitbucket-25253
bitbucket-25253
Posted Monday, February 08, 2010 4:18 PM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 3:04 PM
Points: 5,101,
Visits: 20,200
1. Assuming your back up is FULL RECOVERY. Perform a complete backup
2. Use Set ROWCOUNT to a resonable size (Start low, watch your log file size, and the time to process the batch, display the estimated execution plan and review it before starting your first batch)
3. At the end of each batch of deleting, backup your log file
4. Be sure to save the Full back up and all log backup, you do not want to break the chain in case you need to perform a restore.
And test, test, and test again before using on your Production DB.
If everything seems to be going well, you have obviously overlooked something.
Ron
Please help us, help you -before posting a question please
read
Before posting a performance problem please
read
Post #862125
SQLBOT
SQLBOT
Posted Monday, February 08, 2010 6:26 PM
SSChasing Mays
Group: General Forum Members
Last Login: Monday, February 18, 2013 3:22 PM
Points: 626,
Visits: 835
I would create the index on the columns in your delete statement's where clause.
It won't take that long if the disk is fairly fast and your delete will be far far faster.
If you have enterprise edition you can create the index with online=on.
If you do go that route, make sure to calculate the space the index will consume when you add it.
Craig Outcalt
Tips for new DBAs:
http://www.sqlservercentral.com/articles/Career/64632
My other articles:
http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
Post #862152
SQLBOT
SQLBOT
Posted Monday, February 08, 2010 6:27 PM
SSChasing Mays
Group: General Forum Members
Last Login: Monday, February 18, 2013 3:22 PM
Points: 626,
Visits: 835
I would create the index on the columns in your delete statement's where clause.
It won't take that long if the disk is fairly fast and your delete will be far far faster.
If you have enterprise edition you can create the index with online=on.
If you do go that route, make sure to calculate the space the index will consume when you add it.
Craig Outcalt
Tips for new DBAs:
http://www.sqlservercentral.com/articles/Career/64632
My other articles:
http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
Post #862153
Jeff Moden
Jeff Moden
Posted Monday, February 08, 2010 9:28 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 32,906,
Visits: 26,789
Amit Singh (2/8/2010)
copying to another table would require another 135 GB on the disk ..... do we have that much space ...
you could try a BCP with queryout to output to a file (smaller in size, faster as well) with the data that you eventually want to keep.
then you truncate the table
then you BCP in the file from step 1
just some thoughts....
That would actually take more disk space because the data would materialize as characters instead of datatypes.
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #862185
Jeff Moden
Jeff Moden
Posted Monday, February 08, 2010 9:30 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 32,906,
Visits: 26,789
Sanz (2/8/2010)
I have a huge table in my database. I need to delete records from this table. There are no indexes on this table. If I create an index on one of the columns the deletion process will run faster but the index creation will take a long time.
I need to know the best method to delete rows from this table.
Thanx in Advance !
Do you have a primary key on this table? I'm asking because a lot of folks don't know that a Primary Key also makes an index.
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #862186
« Prev Topic
|
Next Topic »
22 posts, Page 1 of 3
1
2
3
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
