During a recovery, transactions are committed and rolled back. After the recovery process completes, a checkpoint will be issued which causes the lazywriter to flush any changes to disk. Setting this to false disables this behavior.

offline

The offline option allows you to make a database inaccessible to users. This is normally used for databases that are located on removeable media. A database can only be placed in an offline mode if no user is connected. During the recovery process, any offline databases are not recovered.

published

The published option controls whether a database is availabell to participate in publish – subscribe replication. This normally gets enabled when replication is defined. The first time this option is enabled for any database, a repl_subscriber user is added to the database.

read only

The read only only does exact what its name implies – places a database in a read only mode. All data modification commands are disallowed and logging to the transaction log is disabled. This setting is useful for databases that are used strictly for querying purposes as the lack of logging will increase the response time.

recursive triggers

The recursive triggers option allows recursive firing of triggers. This option is the same as the server server-level option of nested triggers, but allows a more granular level of definition. If you need triggers to fire recursively, you should enable them at a database level instead of server wide.

select into/bulkcopy

Select into and bulk copy are two commands in a class termed minimally logged transactions. This means that when a select into or bulk copy are performed, the data that is manipulated is not written to the transaction log. This is desirable for these types of operations in order to achieve maximum performance.

This has a significant impact on recoverability. In any production database, this option should never be turned on unless you have no need of the transaction log. If you want to backup the transaction log, you should leave this disabled. Enabling select into/bulk copy allows nonlogged transactions to be performed in the database. If a nonlogged transaction is performed, a transaction log can not be backed up until after the next full database backup.

single user

Single user mode restricts access to one user at a time. No concurrent connections are allowed even by the same user. In a production system, this option should be disabled. However for some administrative functions such as DBCCs, single user mode is desirable.

subscribed

The subscribed option controls whether a database will participate in publish – subscribe replication. This is normally enabled by the replication definition process and will give the repl_publisher dbo access to the database.

SQL Server 7.0 Server, Database, and Query options 1 2 3 4 5 6 7 8