| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2007-02-26 : 10:16:15
|
| BalajiJaganathan writes "Hi people,I am having a scenario where my table has 1 billion records and i want to delete in a single sql statement.delete from <tablename> is not executing successfully,,since my log file cannot hold this transaction.Is there any other way to do that,,,it should be other thantruncate,,trial and error using top operatorsmy straight question is "Is there any way to delete the data without writing to the log file?"Any answers to this will be great" |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-02-26 : 10:21:39
|
Yes. use truncate table. But since you don't want that . . . try drop table <tablename>
KH |
 |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-02-26 : 10:28:53
|
| Try to resolve the root cause, not patch it.Since you don't have log space to hold transaction, even if there is anyway to delete the data without logging it, you are going to need log space sooner or later.1. Backup and truncate the log if using Full Recovery model2. Purchase additional disk space.BTW, there is no way you can do it without logging it !Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-02-26 : 10:30:38
|
| TRUNCATE TABLE MyBillionRecordTableNameHereWhy are you so reluctant to use TRUNCATE? Because of homework or job interview question?Peter LarssonHelsingborg, Sweden |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-02-26 : 10:45:55
|
quote:
Originally posted by Peso
TRUNCATE TABLE MyBillionRecordTableNameHereWhy are you so reluctant to use TRUNCATE? Because of homework or job interview question?Peter LarssonHelsingborg, Sweden
May be because his table has foreign key constraints and Truncate Table doesn't allow him to delete the data...but latter two possibilities also can not be overlooked.  Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-02-26 : 10:49:54
|
quote:
Originally posted by BalajiJaganathan
it should be other thantruncate,,trial and error using top operators
I am more confused over this.Peter LarssonHelsingborg, Sweden |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-02-26 : 10:59:07
|
Even I am confused. If there are 1 billion records and he want to delete them in single statement, how the TOP is gonna help him ?Anyway, seems like poster himself is confused  Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2007-02-26 : 11:30:55
|
| put the db in bulk logged mode and switch it back to full mode after that..might help. |
|
|
|
|
Any idea