Can I turn off logging?

In SQL Server, it seems that the transaction log adds a lot of overhead (writing all changes) as well as uses space on my disks. I have to load data and it doesn't matter if it is in a transaction. I can reload it if there are issues.

Can I turn off logging in the transaction log?

more ▼

asked Oct 21, 2009 at 05:30 PM in Default

avatar image

Steve Jones - Editor ♦♦
5.2k 79 94 87

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

You can uses either a simple recovery model or bulk. This will reduce the amount of transaction that your kept in the tran log.

In the simple model transaction are still writting to the log for consistance within the database, but removed once the transaction has finished.

Further info below. Remb that using simple DOES limit your recovery options when/if you have to recover your database.


more ▼

answered Oct 21, 2009 at 05:46 PM

avatar image

10.9k 27 37 37

(comments are locked)
10|1200 characters needed characters left

Using the simple recovery model, the log continues to grow only until the next checkpoint. Therefore, if space is a critical issue, you can issue manual CHECKPOINT statements to force log reuse. This (hopefully) is rarely necessary in a production or even test environment, but we once had to use this technique when working in a development lab that was very short on available resources. We had a series of large transactions and inserted manual checkpoints in between them.

Also, if you are able to convert some of your tasks to bulk operations, that would reduce the amount of information logged.

more ▼

answered Oct 21, 2009 at 06:31 PM

avatar image

Tom Staab ♦
14.5k 7 15 21

(comments are locked)
10|1200 characters needed characters left

Prior versions had a "Truncate Log On Checkpoint".

The newer versions of SQL Server have modes - Simple Recovery, Full and Bulk Logged. Read more here:


To stop logging your transactions (well, you don't really stop logging them), you change to Simple Recovery Mode, and the transaction log is truncated at every checkpoint.

Transaction Logging in SQL Server is not really something that you can turn off temporarily. If you switch from Simple Recovery to Bulk Logged, the only transactions that will be in the log will be the ones after you changed your setting.

more ▼

answered Oct 21, 2009 at 06:54 PM

avatar image

Raj More
1.8k 83 89 90

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 21, 2009 at 05:30 PM

Seen: 14997 times

Last Updated: Oct 21, 2009 at 05:30 PM

Copyright 2018 Redgate Software. Privacy Policy