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.
Creating a larger system procedures device (sysprocsdev)
Determine which device or devices you need to remove.
WARNING! Do not remove any device that is in use by database other than sybsystemprocs, or you will destroy 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; you will need them 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 Step1.
Remove the device or devices using sp_configure:
sp_configure "allow updates", 1 go delete sysdevices where name in ("devname1", "devname2", ...) go sp_configure "allow updates", 0 go
The where clause contains the list of device names returned by the query in step 1.
Each device name must have quotes. For example, “devname1”, “devname2”, and so on.
If any of the named devices are OS files rather than raw partitions, use the appropriate OS commands to remove those files.
Remove all files for the list of d.phyname returned in step 1.
File names cannot be complete path names. If they are relative paths, they are relative to the directory from which your server was started.
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 where /sybase/work/ is the full, absolute path to your system procedures device:
1> use master 2> go
1> disk init 2> name = "sysprocsdev", 3> physname = "/sybase/work/sysproc.dat", 4> size = 51200 5> go
Server versions 12.0.x and later accept but do not require "vdevno=number"
.
In server versions earlier than 12.0.x, the number for vdevno must
be available. For information about determining whether vdevno is
available, see the System Administration Guide.
The size you provide should be the number of megabytes of space needed for the device multiplied by 512. disk init requires the size to be specified in 2K pages. In this example, the size is 112MB (112 x 512 = 57344). For more information on disk init, see the Reference Manual.
Create a sybsystemprocs database of the appropriate size on that device, for example:
1> create database sybsystemprocs on sysprocsdev = 112
2> go
Run the installmaster script in the old Sybase installation directory, as follows:
isql -Usa -Ppassword -Sserver_name -i/old_dir/ scripts/installmaster -oinstallmaster.out