Unlogged transactions

Unlogged transactions

Post by Scott » Sat, 18 Jan 2003 02:52:50



I have a table which is frequently subjected to large
bulk inserts, updates, and deletions.  The data is 100%
temporary, meaning I will never have a need to restore it
from a backup.

My problem is this.  Several hundred million updates are
made to this table daily, causing the transaction log to
grow by several GB/hr.  Is there any way I can keep SQL
from logging these transactions?

Using SQL 2K

Thanks,
Scott

 
 
 

Unlogged transactions

Post by Anith Se » Sat, 18 Jan 2003 03:01:07


You cannot turn off the transaction log completely. However you can try
inserting into the table using minimally logged operations like BCP,
Bulk Insert, SELECT INTO etc.

Also, if you are on SQL 2000, depending on your requirements, you can set
the recovery model to simple. See details about recovery models in SQL
Server Books Online.

--
- Anith
(Please respond only to newsgroups)

 
 
 

Unlogged transactions

Post by Alex » Sat, 18 Jan 2003 08:01:39


Did you try to issue periodically or after big updates:

backup log YourLogName with truncate only

?

HTH
Alex


Quote:> I have a table which is frequently subjected to large
> bulk inserts, updates, and deletions.  The data is 100%
> temporary, meaning I will never have a need to restore it
> from a backup.

> My problem is this.  Several hundred million updates are
> made to this table daily, causing the transaction log to
> grow by several GB/hr.  Is there any way I can keep SQL
> from logging these transactions?

> Using SQL 2K

> Thanks,
> Scott

 
 
 

Unlogged transactions

Post by Alvin Zhao[MSF » Sat, 18 Jan 2003 14:59:12


Hi Scott,

A workaround is to put the recovery mode to simple so that any log will be
truncated after a checkpoint and the log will not take up much space. If
you don't want to put the database that the table is in into simple
recovery mode, you can put the table into tempdb. The tempdb database
always uses the simple recovery model, it cannot be switched to another
recovery model. Log truncation always occurs on a checkpoint in tempdb.
Remember that the tempdb will be cleaned up every time SQL Server is
started. So if you need to keep the data of the table, you need to copy
them out from time to time.

Sincerely,

Alvin Zhao
Microsoft Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 
 
 

1. Unlogged transactions

I have a table which is frequently subjected to large
bulk inserts, updates, and deletions.  The data is 100%
temporary, meaning I will never have a need to restore it
from a backup.

My problem is this.  Several hundred million updates are
made to this table daily, causing the transaction log to
grow by several GB/hr.  Is there any way I can keep SQL
from logging these transactions?

Thanks,
Scott

2. Help!: University Ingres & EQUEL

3. What are unlogged transactions

4. Microsof VFP Documentation Omission

5. unlogged database

6. PRESS: Informix in the News

7. Using Delete and wanting unlogged

8. Fix for non-blocking connections in libpq

9. SQL 6.5: Determining whether unlogged changes have taken place

10. Using Delete and wanting unlogged PART II

11. dbimport unlogged

12. logical log entries for unlogged db

13. Transaction Replication Setup Correctly But Not Seeing Transactions