Skip to Content
0

ALTER DATABASE statement silently fails

Feb 14, 2017 at 01:22 PM

88

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Bret Halford
Feb 20, 2017 at 07:31 PM
0

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

Cheers,
Bret Halford
SAP Product Support

Share
10 |10000 characters needed characters left characters exceeded
Avinash Kothare Feb 14, 2017 at 07:22 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded