How to turn off mixed data and log in Sybase ASE 15.7?

Question

When I use sp_helpdb in Sybase ASE 15.7 my result:

db_abc      2054.0 MB   sa  trunc log on chkpt, mixed log and data
db_def      1030.0 MB   sa  trunc log on chkpt, mixed log and data

Could I turn off mixed log and data? I tried with sp_dboption db_abc , 'mixed log and data', false But show error Sybase Database Error: The database option does not exist or you cannot set the option. While sp_dboption db_abc , 'abort tran on log full', false in other case is Ok.

Answer 1

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)