Take the 2-minute tour ×

Oracle has SQL commands that one can issue so that a transaction does not get logged. Is there something similar for SQL Server 2008?

My scenario: We need Tx logs on servers (Dev, QA, Prod), but maybe we can do without them on developer machines.

share|improve this question
have you tried using views for your calculated data? –  user705482 Apr 13 '11 at 7:19
Sorry to hijack your question, but how to disable transaction logging in oracle for a particualr procedure/query? Thank you! –  Victor Aug 25 '11 at 16:02
@Kaushik you're better off asking this as it's own question. –  Raj More Aug 25 '11 at 17:23
"so that a transaction does not get logged" this is not true if you are referring to the NOLOGGED option. –  a_horse_with_no_name Jul 18 '13 at 10:28
add comment

6 Answers

up vote 46 down vote accepted

You can't do without transaction logs in SQL Server, under any circumstances. The engine simply won't function.

You CAN set your recovery model to SIMPLE on your dev machines - that will prevent transaction log bloating when tran log backups aren't done.


share|improve this answer
Just a BIG heads up: This answer is 100% technically correct. But it's solely something you'd want to use on your DEVELOPER machines or when you do NOT care about being able to backup your data. See sqlservervideos.com/video/logging-essentials and sqlservervideos.com/video/shrinking-log-files for more info and background. –  Michael K. Campbell Apr 26 '12 at 22:39
add comment

SQL Server requires a transaction log in order to function.

That said there are two modes of operation for the transaction log:

  • Simple
  • Full

In Full mode the transaction log keeps growing until you back up the database. In Simple mode: space in the transaction log is 'recycled' every Checkpoint.

Very few people have a need to run their databases in the Full recovery model. The only point in using the Full model is if you want to backup the database multiple times per day, and backing up the whole database takes too long - so you just backup the transaction log.

The transaction log keeps growing all day, and you keep backing just it up. That night you do your full backup, and SQL Server then truncates the transaction log, begins to reuse the space allocated in the transaction log file.

If you only ever do full database backups, you don't want the Full recovery mode.

share|improve this answer
This is bit wrong: you need to backup the log to (hopefully) stop the transaction log growing. Of course, if you have an open transaction or a huge transaction, then the log will grow because the internal log file allocations (VLFs) cannot be reused or you need more. That's a quick overview BTW, not chapter and verse –  gbn Oct 31 '12 at 14:12
@gbn You don't backup the log to stop the log from growing. i mean you do, but that's not the point of the feature. Microsoft didn't create a transaction log in order to play the game "consume your entire hard drive because you forgot to backup the log". The reason the log keeps growing (and then requires backing up to stop it from growing), is so that you can backup just the log. –  Ian Boyd Oct 31 '12 at 21:07
add comment

There is a third recovery mode not mentioned above. The recovery mode ultimately determines how large the LDF files become and how ofter they are written to. In cases where you are going to be doing any type of bulk inserts, you should set the DB to be in "BULK/LOGGED". This makes bulk inserts move speedily along and can be changed on the fly.

To do so,

USE master ;

To change it back:

USE master ;

In the spirit of adding to the conversation about why someone would not want an LDF, I add this: We do multi-dimensional modelling. Essentially we use the DB as a large store of variables that are processed in bulk using external programs. We do not EVER require rollbacks. If we could get a performance boost by turning of ALL logging, we'd take it in a heart beat.

share|improve this answer
Thanks for this. We use the DB in exactly the same way. We couldn't care less about being able to roll back. Pure performance is all we are after –  Dan Aug 1 '12 at 10:44
add comment

What's your problem with Tx logs? They grow? Then just set truncate on checkpoint option.

From Microsoft documentation:

In SQL Server 2000 or in SQL Server 2005, the "Simple" recovery model is equivalent to "truncate log on checkpoint" in earlier versions of SQL Server. If the transaction log is truncated every time a checkpoint is performed on the server, this prevents you from using the log for database recovery. You can only use full database backups to restore your data. Backups of the transaction log are disabled when the "Simple" recovery model is used.

share|improve this answer
Well, space issues - some dev's have older machines and we can do without. More to the point, I am curious if this is at all possible! I was under the impression that the "Truncate Log on Checkpoint" option was only set using the Simple recovery mode in SQL 2008. Is there another way? –  Raj More Jun 15 '09 at 15:02
@Tapori, they're the same thing. They renamed it in 2000. –  zvolkov Jun 15 '09 at 16:18
add comment

I've actually hit a situation in which not writing to the transaction logs makes total sense.

I have some tables that I use to index data. The data in the tables is completely calculated data and needs to be periodically updated with the latest information. They have a cross join sort of nature and can become very large sometimes. They don't need to be backed up, because they can be recalculated from the other tables at any time. The recalculation, however, fills the transaction logs up quickly and I sense the calculations could move faster if the DB wasn't trying to keep track of the log. I can back up the logs to keep them small, but I still end up backing up huge amounts of data that could be derived from other data.

As I am writing this, I am remembering that multiple data files can be used for a single DB. I will see if I can place the calculated tables into a different file that is backed up separately and eliminated more frequently.

It would still be useful to me, however, if MS would provide a means for indicating that certain tables should not be logged. If anyone happens to know that this is possible, please describe.

share|improve this answer
@Chris You should post this as a new question. –  Raj More Apr 15 '10 at 13:52
add comment

If this is only for dev machines in order to save space then just go with simple recovery mode and you’ll be doing fine.

On production machines though I’d strongly recommend that you keep the databases in full recovery mode. This will ensure you can do point in time recovery if needed.

Also – having databases in full recovery mode can help you to undo accidental updates and deletes by reading transaction log. See below or more details.

How can I rollback an UPDATE query in SQL server 2005?

Read the log file (*.LDF) in sql server 2008

If space is an issue on production machines then just create frequent transaction log backups.

share|improve this answer
add comment

Your Answer


