Full Logging and sp

By default, select into, certain types of alter table, and reorg rebuild are run in minimally logged mode. Before executing these commands, first set the select into/bulk copy database option to true to allow the SAP ASE server to break the dump sequence—that is, to perform operations that prevent the ability to use dump transaction.

When you use the “full logging for [select into | alter table | reorg rebuild | all]” option, the command is run with full logging. Any previously set value of select into/bulk copy becomes irrelevant for any of the now-fully logged commands.

Full logging for fast bcp and parallel sort is not supported, and cannot take place unless you set select into/bulk copy to true.

Once the operation is set to run with full logging, you can run dump transaction/load transaction and recovery for these operations, just like any other fully logged operation.

The syntax to fully log commands that are, by default, minimally logged is:

sp_dboption dbname, “full logging for 
	[select into | alter table | reorg rebuild | all]”,
	true | false

where:

full logging for select into – in order to have a select into proxy table fully logged, set the “full logging for select into” option to true on the remote server that hosts the actual table. If you set the full logging for select into option to false on the server that hosts the actual table, the command is then executed with minimal logging in that database and the dump transaction sequence breaks.
pll create index – enables full logging when a parallel sort is done. Parallel sorting is required when you create a clustered index on a round-robin-partitioned table
full logging for alter table – enables full logging for these versions of alter table that require data movement:
- alter table add column not null
- alter table drop column not null
- alter table modify datatype of not null column
- alter table partition
Other variants of alter table are already executed in fully logged mode.

Note: Changing the locking scheme between an allpages-locked table and a datapages-locked/data rows-locked table by alter table lock requires data movement, however, this behavior is not supported by full logging for alter table.
full logging for reorg rebuild – involves table data movement. This has no impact on the reorg rebuild index command, which is already fully logged. This parameter enables full logging for reorg rebuild table statements. When you do not set this option (or set this option to false), the SAP ASE server executes the reorg rebuild table command with minimal logging.
full logging for all – enables all the above full logging options. Setting all to false disables all the full logging options.

Note: The syntax requires that you specify what you want to fully log; “full logging” by itself is not a valid option.

When you use any of the full logging for option, the command is run with full logging. Any previously set value of select into/bulk copy/pllsort becomes irrelevant for any of the now-fully logged commands. Full logging for fast bcp and parallel sort is not supported and cannot take place unless you set select into/bulk copy/pllsort to true.

Once the operation is set to run with full logging, you can run dump transaction/load transaction and recovery for these operations, just like any other fully logged operation.

The dboption is “full logging for all” and not just “full logging” on its own.

Note: The execution of a fully logged select into, alter table, or reorg rebuild command may require a significant amount of log space to accommodate the transaction log.

Full Logging and sp_dboption