ASE Sysbase - Space issue

[former_member201054]

Hi Experts,

Need to allocate space by using the mentioned isql commands in sysbase ASE 16.3

Error details :

Cannot allocate space for object "js_callouts" in database 'sybmgmtdb' because 'default' segment is full/has no free extents. if you ran out of space in syslogs, dump the transaction log. otherwise,use ALTER DATABASE to increase the size of the segment.

* what should be used for the "device name" and "log_device_name" fields and how to find them out on a windows server.
* what can be the size given in below commands.
* where can I find the transaction log..path please.

$ isql -Usapsa -S<SID> -X -w2000

use master
go
disk resize name = "<device_name>", size = "<size>M"
go

Add the new space on device <device_name> to database saptools:

use master
go
alter database saptools on <device_name>= "<size>M"
go

To increase the saptools' log segment on the <log_device_name> by xxx MB, for example:

use master
go
disk resize name = "<log_device_name>", size = "<size>M"
go
alter database saptools log on <log_device_name> = "<size>M"
go

I would appreciate all of your inputs and help.

Thanks in advance

[former_member188958 · Accepted solution]

1) There is no one procedure. In general, you log into the ASE using some client application. SAP provides a standard simple command-line client called "isql". The typical locations for installation are c:/sybase or c:/sap, but the location is actually arbitrary. One way to find it would be to search for a "SYBASE.bat" file. Once you execute SYBASE.bat, isql should be in your search PATH (if it wasn't already), your DSQUERY (server name specification) should be set, and you should be able to connect to ASE with isql using a login and password.

2) Depends on just how general, and the security model being used. By default, the sp_helpdb and sp_helpdevice stored procedures can be executed by any user. DISK RESIZE requires a login that has sa_role. ALTER DATABASE requires sa_role or that you be the database owner.

3) The database name in the error, "sybmgmtdb"

4) ALTER DATABASE won't tell you which devices the database is already using or how much free space is currently available on them. However, you can just issue an ALTER and ASE will allocate space from what is available on any devices that have been marked as "default" devices - but you probably will want more control than that for anything but a quick-and-dirty test system.

[former_member201054]

Hi Bret,

A Big thank you and appreciate your reply.

Could you please relate the sp and ALTER commands to my case.

* Please tell me the procedure to execute sp * commands on a windows server.. navigation path please.

* from which user should I execute the sp/ALTER commands in general?..Can it be done from Database <sidadm> also?

* what should be the database name used in the field of test..Is it the ASE system ID (or) "sybmgmtdb" appearing in the error?. Please clarify.

* Instead of using the sp command, Can I use the ALTER database command also?

Please provide the detailed inputs..I would appreciate.

Thanks in advance

[former_member188958]

The sp_helpdb stored procedure will show you the logical name of the devices being used by a database and size(s) of the allocations on those devices.

Example:

1> sp_helpdb test
2> go
 name     db_size                    owner      dbid
         created                  durability           lobcomplvl
         inrowlen         status
 -------- -------------------------- ---------- --------
         ------------------------ -------------------- --------------------
         ---------------- ----------------------------
 test            6.0 MB              sa            5
         Oct 12, 2020             full                          0
             NULL         no options set
(1 row affected)
 device_fragments                 size                       usage
         created
         free_kbytes
 -------------------------------- -------------------------- ------------------
         --------------------------------------
         --------------------------------
 datadev1                                3.0 MB              data only
         Oct 12 2020  6:29AM
                     1260
 logdev1                                 3.0 MB              log only
         Oct 12 2020  6:29AM
         not applicable
 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 log only free kbytes = 3058                                                                                                                                             
(return status = 0)

The size is quite flexible, and modern versions of ASE have the ability to shrink databases, so over-allocating isn't the issue it was a decade ago. Your example is already specifying "M" for Megabytes, but you could also specify K for kilobytes or G for gigabytes. M is usually appropriate. The minimum increase is 256 pages (one 'allocation unit'), so "512K" is the minimum size for a server using a 2KB page size, "4M" is the minimum size for a server using a 16KB page size. It is probably reasonable to increase by 10% of the current size of the database - but it really depends on how quickly the database is expected to grow in the future, and we can't tell that from the "cannot allocate space" message - only that we have run out of space.

You can alter a database onto a device for as much free space as there is on the device. If you specify a larger value, ASE will allocate as much space as is free rather than fail.

Example (I ask for 50MB, but am only given 27.5MB):

1> alter database test on master = '50M'
2> go
Extending database by 14080 pages (27.5 megabytes) on disk master

The ASE transaction log is a table in the database. It isn't really a file per-se, though it is stored on log device(s) that are devices that may be implemented as files (or as raw partitions). As shown earlier, sp_helpdb will show you the logical names of the devices being used for the database's log. The sp_helpdevice procedure will show you the physical full path name of the devices.

Example (I've marked the physical device names used in my test database with arrows in this output):

1> sp_helpdevice
2> go
 device_name
         physical_name
         description                                                                                                                                                      
         status       cntrltype          vdevno       vpn_low
         vpn_high
 ----------------------
         ------------------------------------------------------------------------------
         --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         ------------ ------------------ ------------ --------------
         ----------------
 auditdev
         /redhead1/bret/rel1604/data/auditdev
         file system device, special, dsync off, directio on, physical disk, 10.
         00 MB, Free: 0.00 MB
              2               0               5             0
             5119
 auditlog
         /redhead1/bret/rel1604/data/auditlog
         file system device, special, dsync off, directio on, physical disk, 10.
         00 MB, Free: 0.00 MB
              2               0               6             0
             5119
 datadev1
         /redhead1/bret/rel1604/data/datadev1   <-------------------------------
         file system device, special, dsync off, directio on, physical disk, 512
         0.00 MB, Free: 2969.00 MB
              2               0               4             0
          2621439
 datadev2
         /redhead1/bret/rel1604/data/datadev2
         file system device, special, dsync off, directio on, physical disk, 20.
         00 MB, Free: 20.00 MB
              2               0               8             0
            10239
 logdev1
         /redhead1/bret/rel1604/data/logdev1  <------------------------------------
         file system device, special, dsync off, directio on, physical disk, 20.
         00 MB, Free: 17.00 MB
              2               0               7             0
            10239
 logdev2
         /redhead1/bret/rel1604/data/logdev2
         file system device, special, dsync off, directio on, physical disk, 20.
         00 MB, Free: 20.00 MB
              2               0               9             0
            10239
[...]