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

Creating a larger system procedures device (sysprocsdev)

1. 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 and 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.

2. Drop sybsystemprocs:

   use master
   go
   drop database sybsystemprocs
   go
   

3. Remove the device or devices using sp_configure, enter the following for The where clause contains the list of device names returned by the query in step 1:

   sp_configure "allow updates", 1
   go
   

   
   delete sysdevices
   where name in ("devname1", "devname2", ...)
   go
   

   
   sp_configure "allow updates", 0
   go
   

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

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 = 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: Commands.

6. Create a sybsystemprocs database of the appropriate size on that device, for example:

   1> create database sybsystemprocs on sysprocsdev = 112
   

   2> go
   

7. Run the installmaster script in the old Sybase installation directory, as follows:

   isql -Usa -Ppassword -Sserver_name -i/old_dir/
   scripts/installmaster -oinstallmaster.out