Disable Transaction Log
Sql ServerSql Server-2008Sql Server Problem Overview
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.
Sql Server Solutions
Solution 1 - Sql Server
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;
Solution 2 - Sql Server
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.
Solution 3 - Sql Server
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 ;
ALTER DATABASE model SET RECOVERY BULK_LOGGED ;
To change it back:
USE master ;
ALTER DATABASE model SET RECOVERY FULL ;
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.
Solution 4 - Sql Server
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.
Solution 5 - Sql Server
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.