Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
meraydin
Starting Member
2 Posts |
 Posted - 2008-08-14 : 13:04:53
|
| Hi folksI have a table which contains 1.270 Billion rows and I want to delete almost 60% of them. I have tried many suggestions like @@rowcount=1000 while (1=1) delete ... etc. but still no luck. (I got timeout)basically delete condition is this:delete from MyTable where ID in (1,2,3,4)I have an index on ID column. Any help is appreciated. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-08-14 : 13:14:28
|
| switch to simple recovery mode, drop the index, loop the delete, rebuild index._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
 |
|
|
meraydin
Starting Member
2 Posts |
Posted - 2008-08-14 : 13:21:29
|
| Already in simple mode. Is this the only solution? Since rebuilding index on 1.2 billion records is something I want to avoid :( |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-08-14 : 13:34:55
|
| If you are going to remove such a high percentage of the rows, it is probably faster to BCP the rows you want to keep out to a file, truncate the table, and BCP the data back in.Another option would be to create a new table, use DTS or SSIS to copy the rows you want to keep to the new table, rename the tables when you are done, and then drop the old table.Be sure to set a reasonable batch size, like 100,000 rows, to prevent filling the transaction log file.Of course, the table would not be available until the operation is complete.CODO ERGO SUM |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-14 : 15:17:51
|
| I am pretty darn sure that you must have implemented table partition for 1.275 billion rows table otherwise you performance is not good. If you have it,you can switchout unwanted partition. If not, then better do in batches as Michael and spirit suggested(i.e putting in simple recovery model) |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-08-14 : 23:38:24
|
| Partiton doesn't help if partition key is not id column. I'm second for bcp. |
|
|
|
|
|
|
|
|
1.2 Billion rows -- delete specific rows