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 |
|
sona
Yak Posting Veteran
68 Posts |
 Posted - 2002-05-08 : 05:46:31
|
| I am going to perform a Delete Operation on a table with some conditionIs there any way that if can stop the transaction log entries of deletion of record until the deletion is over and then restart it.As my deletion is going to be on a table with large number of records,it will take a long time. |
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2002-05-08 : 06:29:30
|
| AFAIK, you cannot stop logging transactions. There are certain transactions that are not logged (BULK INSERT, I think) but DELETE is always logged.If the amount of data to be deleted is much greater than the amount of data remaining, you may copy the valid data to another table, and truncate the original table. Take care with foreign keys.Or perhaps batch your deletes into smaller chunks. You can use SET ROWCOUNT or logic in your WHERE clause (eg WHERE ID Between 1 AND 100). |
 |
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2002-05-08 : 06:33:00
|
| BOLThe DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log....check thisYou cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger. ======================================Ask to your self before u ask someone |
|
|
|
|
|
|
Transaction log