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.