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 |
|
dmayley
Starting Member
2 Posts |
 Posted - 2008-12-03 : 15:13:29
|
| We are using MS SQL 2005 as a data warehouse. Tables are recreated and data imported weekly. Recovery mode is set to SIMPLE.I have a table where I need to update the data in a column for all rows. Table has approx. 75 million records. When I issue the UPDATE, the log file grows to the point that I run out of disk space.I realize that each update is recorded to the log file. Is there way to exclude the writes to the log file? Maybe, truncate after every million writes?I am sure others have run across this problem...All ideas welcomed.Thanks! |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-03 : 15:20:53
|
| Update in batches rather than 1 single transaction. |
 |
|
|
dmayley
Starting Member
2 Posts |
Posted - 2008-12-03 : 16:14:33
|
quote:
Originally posted by sodeep
Update in batches rather than 1 single transaction.
That's an idea!Then, I can TRUNCATE and SHRINK the log file after each batch of updates.Would really like an UPDATE...WITH NO_LOG option! Thanks sodeep |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2008-12-03 : 16:14:59
|
I was always curious about this, anyone know why in simple recovery there are still writes to the log file for updates?? Doesn't that defeat the purpose of SIMPLE recovery if those log files are not used?
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-03 : 16:19:03
|
quote:
Originally posted by Vinnie881
I was always curious about this, anyone know why in simple recovery there are still writes to the log file for updates?? Doesn't that defeat the purpose of SIMPLE recovery if those log files are not used?
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881
Inactive portion of transaction can't be truncated while transaction is in use even in Simple recovery model. But if batches are used, It will be minimal growth for log file |
|
|
|
|
|
|
SQL UPDATE and LOG FILE growth