The mixed log + data
is not a switch. It is an effect of the way the database is created (and how pages are mixed in the same area). You can not switch it off. You need separate the LOG from DATA to achieve your goal.
Since on the current devices you have both LOG and DATA pages you need to:
- add another device that is planned only for log (
alter database .. log on ..
)
- switch on
single user
mode
- move the log to the new created device with
sp_logdevice
procedure
- switch off
single user
mode
Code example:
1> disk init name = dev1, physname = '/tmp/dev1.dat', size = '200M'
2> go
create database db_abc on dev1 = '200M'
2> go
CREATE DATABASE: allocating 102400 logical pages (200.0 megabytes) on disk 'dev1' (102400 logical pages requested).
Database 'db_abc' is now online.
1> exec sp_dboption 'db_abc', 'trunc log on chkpt', true
2> go
Database option 'trunc log on chkpt' turned ON for database 'db_abc'.
Running CHECKPOINT on database 'db_abc' for option 'trunc log on chkpt' to take effect.
(return status = 0)
1> exec sp_helpdb 'db_abc'
2> go
name db_size owner dbid created durability lobcomplvl inrowlen status
------ ------------- ----- ---- ------------ ---------- ---------- -------- --------------------------------------
db_abc 200.0 MB sa 4 Jul 07, 2017 full 0 NULL trunc log on chkpt, mixed log and data
(1 row affected)
device_fragments size usage created free_kbytes
---------------- ------------- ------------ ------------------- ----------------
dev1 200.0 MB data and log Jul 7 2017 9:56AM 202232
(return status = 0)
1> disk init name = dev2, physname = '/tmp/dev2.dat', size = '100M'
2> go
1> alter database db_abc log on dev2 = '100M'
2> go
Extending database by 51200 pages (100.0 megabytes) on disk dev2
Warning: Using ALTER DATABASE to extend the log segment will cause user thresholds on the log segment within 128 pages of the last chance threshold to be disabled.
1> exec sp_dboption 'db_abc', 'single user', true
2> go
Database option 'single user' turned ON for database 'db_abc'.
Running CHECKPOINT on database 'db_abc' for option 'single user' to take effect.
(return status = 0)
1> exec sp_logdevice 'db_abc', 'dev2'
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
syslogs moved.
The last-chance threshold for database db_abc is now 16 pages.
(return status = 0)
1> exec sp_dboption 'db_abc', 'single user', false
2> go
Database option 'single user' turned OFF for database 'db_abc'.
Running CHECKPOINT on database 'db_abc' for option 'single user' to take effect.
(return status = 0)
1> exec sp_helpdb 'db_abc'
2> go
name db_size owner dbid created durability lobcomplvl inrowlen status
------ ------------- ----- ---- ------------ ---------- ---------- -------- --------------------------------------
db_abc 300.0 MB sa 4 Jul 07, 2017 full 0 NULL trunc log on chkpt, mixed log and data
(1 row affected)
device_fragments size usage created free_kbytes
---------------- ------------- --------- ------------------- ----------------
dev1 200.0 MB data only Jul 7 2017 9:56AM 202248
dev2 100.0 MB log only Jul 7 2017 9:57AM not applicable
--------------------------------------------------------------------------------------------------------------
log only free kbytes = 102000
(return status = 0)