Unlogged transactions

Unlogged transactions

Post by Scott » Fri, 17 Jan 2003 19:32:31



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

 
 
 

Unlogged transactions

Post by SQL Server Development Team [MSFT » Fri, 17 Jan 2003 20:36:05


Scott,

SQL Server 2000 offers three different recovery models (at a database level)
that provide tradeoffs between the amount of logging and potential exposure
to data loss. Please see the "Selecting a Recovery Model" section in Books
Online. In particular, the Simple amd Bulk-Logged models provide lower
levels of logging with varying degrees of data loss protection.

Prakash Sundaresan
Microsoft Corporation
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

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?

> Thanks,
> Scott


 
 
 

Unlogged transactions

Post by Mike Kruchte » Sat, 18 Jan 2003 00:50:02


Set the recovery mode of the database to SIMPLE, and if that isn't feasible
move that table to a new DB and set the new DB recovery mode to SIMPLE.

Mike


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?

> Thanks,
> Scott

 
 
 

Unlogged transactions

Post by SQL Server Development Team [MSFT » Wed, 05 Feb 2003 20:22:50


Scott:

Additionally, if you cannot move this table to a separate database, you can
temporarily switch between bulk-logged recovery and full-recovery modes
without requiring full backup of the database. The only risk is that when
database is in bulk-logged recovery mode, you will lose the ability to do
point-in-time recovery for that duration. Here is the information from Books
Online
"In Microsoft? SQL ServerT 2000, you can switch between full and bulk-logged
recovery models easily. It is not necessary to perform a full database
backup after bulk copy operations complete under the Bulk-Logged Recovery
model. Transaction log backups under this model capture both the log and the
results of any bulk operations performed since the last backup...."

Sunil Agawal
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

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?

> Thanks,
> Scott

 
 
 

1. What are unlogged transactions

Help, I am a new SQL server DBA.
 Occasionally, when my hourly dump of the logfile job runs, I get an error
msg that the log cannot be dumped due to unlogged transactions?? Can anyone
tell me where I can get info on what these are?
Thanks for the help

--
Lisa

2. Here we go again

3. Unlogged transactions

4. weird probs with CompactDatabase HELP!

5. Using Delete and wanting unlogged

6. Job opportunity - NN + software eng. at CRL in UK

7. Using Delete and wanting unlogged PART II

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

9. dbimport unlogged

10. logical log entries for unlogged db

11. unlogged database

12. distributed transaction,nested transactions,sp_getapplock