Sybase – how to check and resize databse

Below in few short steps You find information how to check and resize Sybase database. I hope it will be helpful for You.

1. Log to Sybase

isql -SSID -Usapsa -X -w999

Password:

2. Check and confirm database name

2> select @@servername
3> go
------------------------------
SID

3. Check and verify current DB Extension Configuration

2> sp_dbextend listfull
3> go
type name segment item value status comment
-------- ----------- ------- ------ ----- -------- ---------------------------------------------------
feature server-wide (n/a) (n/a) (n/a) enabled set='Dec 5 2021 1:27PM'
database default (all) growby 0 disabled mod='Dec 5 2021 1:27PM' set='Dec 5 2021 1:27PM'
device default (n/a) growby 0 enabled mod='Dec 5 2021 1:27PM' set='Dec 5 2021 1:27PM'
(return status = 0)
1>

4. Verify current Data Space Usage

SELECT "Database Name" = CONVERT(char(20), db_name(Z.dbid)),
"DB Size MB |" = STR(SUM(((Z.size*16)/1024))) + " |",
"DB Free Space MB |" = STR(SUM(CASE WHEN Z.segmap != 4 THEN ((Z.unreservedpgs)*16/1024) END)) + " |",
"DB Full% |" = STR(100 * (1 - 1.0 * SUM(CASE WHEN Z.segmap != 4 THEN curunreservedpgs(Z.dbid, Z.lstart, Z.unreservedpgs) END)/SUM(CASE WHEN Z.segmap != 4 THEN Z.size END)),7,1) + "% |",
"Log Size MB |" = STR(SUM(CASE WHEN Z.segmap =4 THEN ((Z.size*16)/1024) END)) + " |",
"Free Log MB |" = STR(lct_admin("logsegment_freepages",Z.dbid)*16/1024) + " |",
"Log Full%" = STR (100 * (1 - 1.0 * lct_admin("logsegment_freepages",Z.dbid) / SUM(CASE WHEN Z.segmap = 4 THEN Z.size END)),8,1) + "%"
FROM master..sysusages Z
GROUP BY Z.dbid
ORDER BY db_name(Z.dbid)

5. Review the current device allocation for the affected database with sp_helpdb:

1> sp_helpdb sybmgmtdb
2> go

6. For specific detail on a device, use the stored procedure sp_helpdevice:

2> sp_helpdevice sybmgmtdev
3> go

2> sp_helpdevice sybmgmtlogdev
3> go

7. Before we go next operations it is necessary to verify the free space on the OS – check device path using command: df -h <path>

If space is available now we can extend or create data device:

1. First option - extend device:
disk resize name = '<device_name>', size = '<additional size in M or G>'
go

for example:
1> disk resize name = 'sybmgmtdev', size = '100M'
2> go
Successfully resized the device 'sybmgmtdev' to 256000 kilobytes.

1> disk resize name = 'sybmgmtlogdev', size = '100M'
2> go
Successfully resized the device 'sybmgmtlogdev' to 153600 kilobytes.

Now that space has been created on OS level, we can extend Database:
1> use master
2> go
3> alter database <dbname> on <device_name> = '<additional size>'
4> go

for example:
1> use master
2> go
3> alter database sybmgmtdb on sybmgmtdev = '100M'
4> go
Extending database by 6400 pages (100.0 megabytes) on disk sybmgmtdev

Important, for log files should be use:
1> use master
2> go
3> alter dtabase <DB_Name> log on <Device_Name> = "<Size>"
4> go

for example:
alter database sybmgmtdb log on sybmgmtlogdev = '100M'

2. Second option create device:
1> disk init name = '<device_name>', physname = '<full path and name of new file>', size = '<size in M or G>'
2> go

Cancel Reply