ASE Sysbase - Space issue

[#1]

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]

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
[...]