Database-level support for enabling and disabling logging is provided primarily for temporary databases, where entire applications that do not rely on logging can run more efficiently, without changing the code for applications or procedures that create temporary tables
You cannot change the logging mode of system databases, including the model database. You can change the DML logging mode of the system tempdb—and any user temporary databases—to minimal logging. Before doing so, Sybase recommends that learn how changing to minimal logging affects rollback semantics on all applications that use temporary tables. See “Transactional semantics”.
Minimally logged DML is allowed only in a database that has a durability level set to no_recovery or at_shutdown. You must set the database’s select into option to on for minimal logging to take effect.
You can alter the default logging mode for a database only from the master database. In addition, the database you are altering must:
Be in single-user mode for user databases
Have the dbo-use only set to true (on) for temporary databases, so that only the database owner can use the databases
If the database is not already in the required mode, the server tries to put the database in that mode. If the attempt is unsuccessful, the server raises an error, prompting the user to explicitly put the database in its correct mode.
The syntax to change the DML logging mode at the database level is:
create [temporary] database database_name
[on {default | database_device [= size]
[, {database_device [= size]...]
[log on {database_device [= size]
[, {database_device [= size]]...]
[with {override | default_location = "pathname"
| [[,]durability = { no_recovery | at_shutdown | full} ]
| [[,]dml_logging = {full | minimal} ]
}...
]
[for {load | proxy_update}]
To change the database-level setting of DML logging in an existing database, use:
alter database dbname
set dml_logging = {full | minimal}
See the Reference Manual: Commands.
