Avoiding logging for user operations

Question: I'd like to be able to speed up operations as much as possible by turning off transaction logging completely, but there does not seem to be a way to do it. Why does SQL Server insist on logging all operations, and is it likely that there will be an option to disable logging in future?

Answer: This is a question that comes up regularly and you’re correct that there is no way to disable the generation of transaction log records.

The best you can do is to make use of minimal logging for a small number of operations when using the Simple or Bulk_Logged recovery models, where only data file page allocations are logged, but individual record inserts are not logged. Examples of operations where you can make use of this are index builds/rebuilds and bulk loads. This cuts down on the amount of transaction log generated for the operation and helps make the operations faster. You can read all about minimal logging during data loads in the excellent whitepaper Data Loading Performance Guide.

There are a few operations in tempdb that are truly non-logged (e.g. anything to do with the version store), but generally operations in tempdb generate less transaction log than in user databases because operations in tempdb do not need to log the after image of the operation, only the before image. The after image in a transaction log record is only used for replaying transactions during crash recovery, and as tempdb is not crash-recovered, no replay will ever be performed in tempdb.

Apart from those few operations in tempdb that are non-logged, everything else has to be logged to some degree because SQL Server has to be able to roll back an operation in a database if something goes wrong.

Imagine a situation where SQL Server is part way through an update of 1 million records when the power is accidentally cut to the server. When SQL Server restarts, if there were no log records generated for the update, how can you or SQL Server know how far the update operation progressed before the crash? The simple answer is that you can’t. This means the data is in an inconsistent state and is essentially unusable. The generation of log records allows the update to be rolled back and transactional consistency is maintained.

Even in tempdb, logging is required. If something causes SQL Server to want to roll back an operation in tempdb, but there are no log records, then tempdb is transactionally inconsistent – and SQL Server would have to shut down as it would now know which parts of tempdb were in use or not.

It's always easy to come up with examples of how a non-logged paradigm breaks down, and for that reason I don’t believe we’ll see a non-logged option for SQL Server operations in future.

Discuss this Blog Entry 1

on May 20, 2013

"It’s always easy to come up with examples of how a non-logged paradigm breaks down, and for that reason I don’t believe we’ll see a non-logged option for SQL Server operations in future."

That is simply not the case that every database operation must be logged and our DBEs wish SQL Server would give us the ability to turn off transaction logging.

There are processes that my company builds that allow users to massage data in a Staging environment until it is ready to be promoted to Production.

In the Staging environment the data manipulation is very intense, half a million to a million rows are are loaded, automated cleaning takes place then the rows are compared to Production data.

If, during anytime in the process SQL Server were to go down then we would NOT care. The process can always be restarted and we lose nothing.

It cannot even be argued that we lose time, because the frequency of a server crash is so rare that the time we would save NOT logging transactions would be greater than having to restart the process.

When the data is loaded into Production tables those transactions could be logged in the event of a server crash.

We have many types of these bulk data operations that occur in Staging environments and from our perspective the ability to turn OFF transaction logging would be greatly beneficial.

Please or Register to post comments.

What's SQL Server Questions Answered?

Practical tips and answers to many of your questions about SQL Server including database management and performance issues.

Contributors

Paul S. Randal

Paul Randal worked on Microsoft's SQL Server team for nine years in development and management roles, writing many of the DBCC commands. Randal was ultimately responsible for SQL Server 2008'...

Kimberly L. Tripp

Kimberly L. Tripp has been working with SQL Server since 1990, and she’s worked as a consultant, trainer, speaker, and writer specializing in core SQL Server performance tuning and availability...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×