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. The minimum size of sybsystemprocs is 184MB; the recommended size is 196MB.

1. Determine which device you must remove:

   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 devices that are used by sybsystemprocs and other databases.

   Make a note of the names of the devices to use in subsequent steps.

   Warning!   Do not remove any device that is in use by a database other than sybsystemprocs, or you will destroy that database.
2. Drop sybsystemprocs:

   1> use master
   2> go
   1> drop database sybsystemprocs
   2> go

   Note: In versions earlier than 15.x, use sysdevices to determine which device has a low through high virtual page range that includes the vstart from step 2.

   In version 15.x, select the vdevno from sysusages matching the dbid retrieved in step 1.

3. Remove the device:

   1> sp_configure "allow updates", 1
   2> go
   1> delete sysdevices
       where name in ("devname1", "devname2", ...)
   2> go
   1> sp_configure "allow updates", 0
   2> go

   The where clause contains the list of device names returned by the query in step 1.

   Note: 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.

4. Remove all files that were returned in the d.phyname list.
   Note: File names cannot be complete path names. If you use relative paths, they are relative to the directory from which your server was started.
5. 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 = 200M
   5> go

   Note: Server versions 12.0.x and later accept, but do not require "vdevno=number". 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 2KB pages. In this example, the size is 112MB (112 x 512 = 57344). For more information on disk init, see the Reference Manual: Commands.
6. Create a sybsystemprocs database of the appropriate size on that device, for example:

   1> create database sybsystemprocs on sysprocsdev = 180
   2> go

7. In the old server installation directory, enter:

   isql -Usa -Ppassword -Sserver_name -i %SYBASE%\ASE-15_0\scripts\instmstr