x

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

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 77 79 82

(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.

http://msdn.microsoft.com/en-us/library/aa173531%28SQL.80%29.aspx

more ▼

answered Oct 21, 2009 at 05:46 PM

sp_lock gravatar image

sp_lock
9.2k 25 28 31

(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

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

(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:

http://msdn.microsoft.com/en-us/library/ms189275.aspx

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

Raj More gravatar image

Raj More
1.7k 80 82 84

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x344
x68

asked: Oct 21, 2009 at 05:30 PM

Seen: 10876 times

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