cancel
Showing results for 
Search instead for 
Did you mean: 

ALTER DATABASE statement silently fails

neal_stack2
Explorer
0 Kudos
192

Hello,

I've noticed that the ALTER DATABASE statement can silently fail. I tested this on:

Adaptive Server Enterprise/15.7/EBF 25309 SMP SP135 HF1/P/x86_64/Enterprise Linux/ase157sp133x/3936

My server is configured to have a page size of 4K. If I issue an ALTER DATABASE statement with a size below the documented minimum (ALTER DATABASE), it silently fails to alter the database:

1> select @@maxpagesize
2> go            
 ----------- 
        4096 
(1 row affected)
1> ALTER DATABASE db1 log on d1_log='512K'
2> go
1> sp_helpdb db1
2> go
 name db_size       owner dbid created      durability lobcomplvl inrowlen status             
 ---- ------------- ----- ---- ------------ ---------- ---------- -------- ------------------ 
 db1         7.0 MB sa      23 Aug 03, 2016 full                0     NULL trunc log on chkpt 


(1 row affected)
 device_fragments               size          usage                created                   free kbytes      
 ------------------------------ ------------- -------------------- ------------------------- ---------------- 
 d1                                    6.0 MB data only            Aug  3 2016  8:58PM                   2712 
 d1_log                                1.0 MB log only             Aug  3 2016  8:58PM       not applicable   
                                                                                                                
 -------------------------------------------------------------------------------------------------------------- 
 log only free kbytes = 992                                                                                     
(return status = 0)

If I try the same on an ASE server with 16K pages, the ALTER DATABASE statement fails and raises an error for some invalid sizes (but not 512K):

1> select @@maxpagesize
2> go           
 ----------- 
       16384 
(1 row affected)
1> ALTER DATABASE delphix log on d1_log="1M"
2> go
Msg 5015, Level 16, State 1:
Server 'NSTACK_16K', Line 1:
CREATE or ALTER DATABASE failed. The size specified must be 4 megabyte(s) or greater.
1> ALTER DATABASE delphix log on d1_log="512K"
2> go
1>

Is this a known issue with the "ALTER DATABASE" command?

Thanks,

Neal

View Entire Topic
former_member188958
Active Contributor
0 Kudos

I've opened CR 805624 on this issue, and published KBA 2429256 to document it.

Cheers,
Bret Halford
SAP Product Support