Prev: I'm a New bie to SQL SERVER 2005
Next: how to convert int to datetime
From: imani_technology_spam on 29 Sep 2009 19:03
We have a table that contains billions of rows. When we try to UPDATE
that table, we overload the transaction log. What are some ways to
avoid this? Is there a way to "turn off" the transaction log
temporarily?
From: Thomas Arthur Seidel on 30 Sep 2009 17:37
Possibly it is worth to try a bigger machine? SQL 2008 on Win2008Server with lots
of RAM might solve a performance problem like yours, its worth a try, isn't it?

TAS


imani_technology_spam(a)yahoo.com wrote:
> We have a table that contains billions of rows. When we try to UPDATE
> that table, we overload the transaction log. What are some ways to
> avoid this? Is there a way to "turn off" the transaction log
> temporarily?
From: Plamen Ratchev on 30 Sep 2009 17:56
You can update on smaller batches to avoid transaction log growth:
http://www.tek-tips.com/faqs.cfm?fid=3141

--
Plamen Ratchev
http://www.SQLStudio.com
From: Erland Sommarskog on 30 Sep 2009 18:00
imani_technology_spam(a)yahoo.com (imani_technology_spam(a)yahoo.com) writes:
> We have a table that contains billions of rows. When we try to UPDATE
> that table, we overload the transaction log. What are some ways to
> avoid this? Is there a way to "turn off" the transaction log
> temporarily?

For UPDATEs no. An alternative may be to copy the data to a new
table with SELECT INTO, which is minimally logged, meaning that only
page allocations are logged. Note that this applies only in simple and
bulk_logged recovery.

Obviously this approach comes with its own set of problems.

Plamen's suggestion to update in smaller batches is also worth
investigating.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Tom van Stiphout on 1 Oct 2009 00:11
On Tue, 29 Sep 2009 16:03:16 -0700 (PDT),
"imani_technology_spam(a)yahoo.com" <imani_technology_spam(a)yahoo.com>
wrote:

You already got some good suggestions, but to answer your direct
question: no, transaction logging cannot be turned off as far as I
know. As I understand it, it's such a fundamental part of database I/O
that even a switch to turn it off (e.g. when in development mode) was
not possible.

-Tom.
Microsoft Access MVP


>We have a table that contains billions of rows. When we try to UPDATE
>that table, we overload the transaction log. What are some ways to
>avoid this? Is there a way to "turn off" the transaction log
>temporarily?
 |  Next  |  Last
Pages: 1 2 3
Prev: I'm a New bie to SQL SERVER 2005
Next: how to convert int to datetime