Increasing device and database capacity for system procedures

If you cannot fit the enlarged sybsystemprocs database on the system procedures device, increase the size of the device and create a new database.

This procedure involves dropping the database. For more information on drop database, see the Reference Manual.

WARNING! This procedure removes all stored procedures you have created at your site. Before you begin, save your local stored procedures using the defncopy utility. See the Utility Guide for more information.

Newer versions of Adaptive Server have a "disk resize" command that extends a device if there is room enough. If your current Adaptive Server version does not have that command but you would like to take advantage of it, you can use these instructions to upgrade first, and enlarge your database later:

Use "dump database" to make a copy of your existing sybsystemprocs database.
Drop the sybsystemprocs database, then recreate it. This removes your existing stored procedures and ensures that sybsystemprocs has enough free space for upgrade.
Upgrade your installation.
Expand the device containing your sybsystemprocs database. This example adds 50 megabytes to a device whose logical name is "sysprocsdev":
```
1> disk resize name=sysprocsdev, size=[50M]
2>go
```
If you dumped sybsystemprocs earlier, use "load database" to restore it, then "online database" to make it ready for use. After doing this, you must recreate all your Sybase-supplied stored procedures by running the install scripts, such as "installmaster".

Determine which device or devices to remove.

WARNING! Do not remove any device that is in use by a database other than sybsystemprocs, because doing so destroys that database.
```
select d.name, d.phyname 
from sysdevices d, sysusages u 
where u.vstart between d.low and d.high 
and u.dbid = db_id("sybsystemprocs") 
and d.status & 2 = 2 
and not exists (select vstart
    from sysusages u2
    where u2.dbid != u.dbid 
    and u2.vstart between d.low and d.high)
```
where:
- d.name – is the list of devices to remove from sysdevices
- d.phyname – is the list of files to remove from your computer
The “not exists” clause in this query excludes any devices that are used both by sybsystemprocs and other databases.

Note the names of the devices; needed in the steps.
Drop sybsystemprocs:
```
use master
go
drop database sybsystemprocs
go
```
In versions of Adaptive Server Enterprise prior to 15.0, you use sysdevices to determine which device has a low through high virtual page range that includes the vstart from Step 2. The device fragment whose vstart you used is on that device

In the 15.0 version of Adaptive Server Enterprise select the vdevno from sysusages matching the dbid retrieved in Step 1.
Find another existing device that meets the requirements for additional free space, or use a disk init command similar to the following to create an additional device for sybsystemprocs:
```
1> use master
2> go
```
```
1> disk init
2> name = "sysprocsdev",
3> physname = "%SYBASE%\data\sysproc.dat",
4> vdevno = 9,
5> size = 51200
6> go
```
where %SYBASE%\data is the path to your system procedures device.
Create a sybsystemprocs database of the appropriate size on that device, for example:
```
1> create database sybsystemprocs on sysprocsdev = 
     112
```
```
2> go
```

Run the instmstr command in the old release directory. For version 12.0:

isql -Usa -Ppassword -i%SYBASE%\%SYBASE_ASE%
     \scripts\instmstr -oinstmster.out

For versions earlier than 12.0:

isql -Usa -Ppassword -i%SYBASE%\scripts\instmstr 
     -oinstmster.out