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?
asked Oct 21, 2009 at 05:30 PM in Default
Steve Jones - Editor ♦♦
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.
answered Oct 21, 2009 at 05:46 PM
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.
answered Oct 21, 2009 at 06:31 PM
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.
answered Oct 21, 2009 at 06:54 PM