禁用事务日志

Oracle 有一些 SQL 命令，可以发出这些命令，这样事务就不会被记录下来。SQLServer2008有类似的东西吗？

我的场景: 我们需要服务器(Dev、 QA、 Prod)上的 Tx 日志，但是也许我们可以在开发人员机器上不使用它们。

185636 次浏览
小开
最佳答案

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;
小开

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.

小开

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.

小开

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.

小开

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.


提交答案