Stored procedures and minimally logged DML

The DML logging setting in a session is inherited from called system procedures and the behavior setting for any set dml_logging command executed in a procedure is inherited by subprocedures. After exiting from the procedure’s scope, regardless of whether set export_options is on, the dml_logging setting in the parent session or parent procedure is restored.

These examples show how these rules are applied to affect the logging mode of tables inside a procedure.

create procedure p1 as
begin
    delete t1 where...

    set dml_logging minimal

    update t2 where...
end
go

set dml_logging default
go

exec p1
go

/*
** Exiting from the procedure restores the
** session's setting to what it was before
** calling the procedure, in this case, the
** logging mode will be back to DEFAULT
** (i.e. FULL).
*/
-- This will operate in logged mode now.
delete t2
go

1. When execution starts in the procedure p1, logging mode is full.

2. delete t1 is performed in fully logged mode, and then the procedure’s session-level logging mode is changed to minimal.

3. update t2 is performed in minimally logged mode.

4. Upon exit from p1, when control returns to the outer SQL batch, logging mode setting is back to full. The next delete t2 is performed in fully-logged mode.

set dml_logging minimal
go

exec p1
go

-- This will operate in minimally logged mode.
delete t2
go

As the logging mode for DML statements inside a procedure is affected by the session-level setting of the calling session or procedure, Sybase recommends that you explicitly select the desired logging mode at the start of the procedure body. You may optionally set it back to default when the procedure finishes.

create procedure p1 as
begin
    set dml_logging minimal

    delete t1 where ...
    update t2 where ...

    -- Optionally, reset upon exit
    set dml_logging default
end
go

However, if the DML statements inside a procedure are executed mostly in a single-logging mode (for example, full) but must occasionally run in a different (minimally logged) mode, Sybase recommends that you control the logging DML statements with the session-level setting from the calling procedure or isql session instead of including the logging mode inside the body of the procedure.

In this example, Joe executes the procedure mary.delete_proc, which performs a delete on a table owned by Mary. Joe uses the set command to request minimal logging, but doing so affects the logging mode for tables owned by Mary in the procedure:

isql –Sservername –Umary –Pmaryspwd

create procedure mary.delete_proc as
begin
    delete mary.large_table where ...
end
go

grant exec on mary.delete_proc to joe
go

isql –Sservername –Ujoe –Pjoespwd

-- User 'joe' executes the following SQL:
--
set dml_logging MINIMAL
go

exec mary.delete_proc
go

In this example, minimal logging does not apply to delete mary.large_table, but it dos apply to the update joe.very_large_table when user Joe executes the procedure with default logging settings.

isql –Sservername –Umary –Pmaryspwd

create procedure mary.delete_proc2 as
begin
    set dml_logging MINIMAL

    delete mary.large_table where ...

    update joe.very_large_table where ...
end
go

grant exec on mary.delete_proc2 to joe
go

isql –Sservername –Ujoe –Pjoespwd

exec mary.delete_proc2
go

If a procedure performs DML statements on tables owned by multiple owners, then, depending on which user executes the procedure, the set of tables against which minimally logged DML is executed changes. Only the table owner or user with sa_role can execute the procedure that performs the minimally logged DML on specific tables.

Recompiling a running procedure, or the cached plan of a previously executed procedure, does not affect the logging mode chosen at runtime for individual DML commands that appear in the procedure body. Any set commands that might change the logging mode for a table are taken into consideration at the start of execution of the DML statement.