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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-06-17 : 07:44:46
|
| Amanda writes "I used to have some code that would wrap large transactions in begin and commit, then begin another round - say, every 200,000 rows, or whatever, to keep the tempdb from growing larger.I'm wrestling with that, because our reporting database does not currently have a large enough tempdb to handle the data loads each night from our OLTP database.Can you help me with this?Amanda" |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2005-06-17 : 07:58:24
|
| Why do you use the transactions?What other methods have you considered?You could always buy a bigger disk.-------Moo. :) |
 |
|
|
KLang23
Posting Yak Master
115 Posts |
Posted - 2005-06-17 : 13:46:21
|
| Hi,If you must keep TEMPDB size down, this appears to be a acceptable alternative assuming you can take the risk of one of the transactions failing and not being able to back-out the whole "logical" update. Take a look at not only TEMPDB, but the reporting database log too. By using transactions, you are able to keep the size of both down, and if backout is not a concern, a failed transaction will rollback quickly, free-up the database so you can restore it to the backup you took right before the whole process started. (You did, didn't you? :) )BTW: Yes - disk IS cheap these days. Cheers. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
large data loads, tempdb, and transaction wrapping