Can Transaction log be turned off? 
Author Message
 Can Transaction log be turned off?
Hi,

SQL server 2000.

I thought I read somewhere that the there is a 'nologging' option in DTS.
But now I can't locate any information.  In general, is there an option to
turn off the transaction log anywhere in SQL server? Call me crazy, but for
datawarehousing it just seems to create lots of overhead and disk
utilization.

Joe



Mon, 21 Feb 2005 23:37:39 GMT
 Can Transaction log be turned off?

Joe,

with "bulk insert" and "truncate tbale" there is
some sysntax that allows unlocked transaction
for special purpose, but afaik it is not possible
to turn off the log generally

labntyd

      robert



Tue, 22 Feb 2005 00:09:12 GMT
 Can Transaction log be turned off?
Follow up question.

I am transforming a 8 GB table in a T-SQL script with one static cursor
(fast foward). After business logic is applied my script inserts a new row
to another table. This process is repeated 120 Mil times.

So far the desination table has increased by 5GB.  The transaction log has
increased by 40 GB.  This is causing me all kinds of headaches becuase my
server probably is a little to small for this.  So my question is: what
techniques are there to keep the transaction log to a minimum in size?  I am
not worried about data loss - if the process crashes then I'l restore from
one of my daily backups. It's a datawarehouse so there are no incremental
updates.

Joe


Quote:
> Hi,

> SQL server 2000.

> I thought I read somewhere that the there is a 'nologging' option in DTS.
> But now I can't locate any information.  In general, is there an option to
> turn off the transaction log anywhere in SQL server? Call me crazy, but
for
> datawarehousing it just seems to create lots of overhead and disk
> utilization.

> Joe



Tue, 22 Feb 2005 00:17:38 GMT
 Can Transaction log be turned off?
Joe,

with "bulk insert" and "truncate table" there is
some sysntax that allows unlogged transaction
for special purpose, but afaik it is not possible
to turn off the log generally

labntyd

      robert



Tue, 22 Feb 2005 04:26:23 GMT
 Can Transaction log be turned off?
k,


   WITH TRUNCATE_ONLY

will cut off the inactive part of the log (details -> books online)
   dbcc shrinkfile <filename>
will shrink the logfile (details -> books online)
Then when migrating big amounts of data with t-sql better use
statements with rowset providers
  insert <tablename> select ... from ...
using
  (inner and outer) joins
and when a case distinction is needed
  case when ... then ... when ... them ... else ... end
It's MUCH faster



Tue, 22 Feb 2005 04:36:04 GMT
 Can Transaction log be turned off?
k,


   WITH TRUNCATE_ONLY

will cut off the inactive part of the log (details -> books online)
   dbcc shrinkfile <filename>
will shrink the logfile (details -> books online)
Then when migrating big amounts of data with t-sql better use
statements with rowset providers
  insert <tablename> select ... from ...
using
  (inner and outer) joins
and when a case distinction is needed
  case when ... then ... when ... them ... else ... end
It's MUCH faster



Tue, 22 Feb 2005 04:37:39 GMT
 Can Transaction log be turned off?
k,


   WITH TRUNCATE_ONLY

will cut off the inactive part of the log (details -> books online)
   dbcc shrinkfile <filename>
will shrink the logfile (details -> books online)
Then when migrating big amounts of data with t-sql better use
statements with rowset providers
  insert <tablename> select ... from ...
using
  (inner and outer) joins
and when a case distinction is needed
  case when ... then ... when ... them ... else ... end
It's MUCH faster



Tue, 22 Feb 2005 04:38:19 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. how to turn transaction log off?

2. Turn off logging for a transaction

3. Turning off Transaction Logging

4. How do I turn off the transaction log?

5. Turning transaction logging off

6. how do I turn off the transaction log?

7. Turn off transaction logging

8. Turning off transaction logging from command line?

9. How do I turn off the transaction log

10. How do I turn off the transaction log

11. Turning off transaction logging altogether (not via checkpoint)

12. Turning off Transaction logging


 
Powered by phpBB® Forum Software