Disable Transaction Log

Question

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.

Answer 1

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.

ALTER DATABASE MyDB SET RECOVERY SIMPLE;

Answer 2

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.

Answer 3

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.

Answer 4

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.