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 |