ALTER DATABASE statement silently fails

[neal_stack2]

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

[former_member188958 · Accepted solution]

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

Cheers,
Bret Halford
SAP Product Support

[former_member89972]

I observed the same behavior for my 8K page latest ASE SP02PL05 on AIX.

So looks like a bug !!

I created a dummy database double the size you reported and then tried to alter the log.

My session results :

1> select @@version

2> go

----------------------------------------------------------------------------------------------------------------------------------

Adaptive Server Enterprise/16.0 SP02 PL05/EBF 26184 SMP/P/RS6000/AIX 7.1/ase160sp02pl05x/2714/64-bit/FBO/Sun Dec 4 08:39:51 2016

1> select @@maxpagesize

2> go

-----------

8192

1> sp_helpdb dummydb

2> go

name db_size owner dbid created durability lobcomplvl inrowlen status

------- ------------- ----- ---- ------------ ---------- ---------- -------- --------------

dummydb 14.0 MB sa 11 Feb 14, 2017 full 0 NULL no options set

(1 row affected)

device_fragments size usage created free_kbytes

---------------- ------------- --------- ------------------- ----------------

P5D_data001 12.0 MB data only Feb 14 2017 1:05PM 5488

P5L_log001 2.0 MB log only Feb 14 2017 1:05PM not applicable

--------------------------------------------------------------------------------------------------------------

log only free kbytes = 2032 (return status = 0)

1> alter database dummydb log on P5L_log001='512K'

2> go

1> select @@error

2> go

-----------

0

(1 row affected)

1> alter database dummydb log on P5L_log001='1M'

2> go

Msg 5015, Level 16, State 1:

Server 'PHI5_SB_16', Line 1:

CREATE or ALTER DATABASE failed. The size specified must be 2 megabyte(s) or greater.

1> select @@error

2> go

-----------

5015 (1 row affected)

My hunch is a failing string comparison in the code.

alter database fails silently for sizes from '0K' upto and including '1023K'.

1024K (same as 1M) fails as expected.

SAP folks please note and raise CR if need be.

Avinash