Restoring sybsystemprocs with installmaster

This section assumes that your sybsystemprocs database exists, but is corrupt. If sybsystemprocs does not exist, you must create it using create database.

To restore sybsystemprocs with installmaster:

1. Check to see which devices currently store sybsystemprocs:

   select  lstart,
   size / (power(2,20)/@@maxpagesize) as 'MB',
   d.name as 'device name',
   case when segmap = 4 then 'log'
   when segmap & 4 = 0 then 'data'
   else 'log and data'
   end as 'usage'
   from sysusages u, sysdevices d
   where d.vdevno = u.vdevno
   and d.status & 2 = 2
   and dbid = db_id('sybsystemprocs')
   order by 1
   

   The result probably shows sybsystemprocs all on one disk fragment, and having log and data as its usage, but you may have a more complex layout than that. Save this query’s results for later use.

2. Drop the database:

   drop database sybsystemprocs
   

   If that succeeds and the device is undamaged, go to step 3.

   • If sybsystemprocs is badly corrupted, the drop database may fail. Manually remove the database by deleting the information that identifies it:

     sp_configure ‘allow updates’, 1
     go
     delete from sysusages
     where dbid = db_id('sybsystemprocs')
     delete from sysdatabases
     where name = 'sybsystemprocs'
     go
     sp_configure 'allow updates', 0
     go
     

   • If the physical disk is damaged, drop the device:

     sp_dropdevice name_of_sybsystemprocs_device
     

   If you manually removed sybsystemprocs, re-created the sybsystemprocs device, shut down Adaptive Server using shutdown with nowait. If you dropped the sybsystemprocs device and it was not a raw partition, remove the physical file. Restart Adaptive Server.

3. Re-create the sybsystemprocs device. If you dropped the sybsystemprocs device, use disk init to create a new one. Then re-create sybsystemprocs using one of the methods below using the results you saved from step 1.

   If you plan to load a backup copy of sybsystemprocs, you can include the for load option with the create database or alter database commands. However, you must use load database to load the backup copy before it is used for any other purpose.

   • If the displayed usage was all log and data, create a simple database using:

     create database sybsystemprocs on device_name = N
     

     where N is the total size of the device. You may find that you need to create the new database on multiple devices to get the size you need.

   • If the displayed usage contains any log or data entries, use create database and alter database to re-create the same layout. You can group contiguous data or log sections on a single device, but avoid mixing log with data. Re-create the first group of data and log sections:

     create database sybsystemprocs 
     on device_1 = M
     log on device_2 = N
     

     where M is the sum of the first group of data sizes and N is the sum of the first group of log sizes. For each successive group, repeat this process using alter database instead of create database to enlarge the database.

4. Run the installmaster script to create the Sybase-supplied system procedures.

   On UNIX platforms:

   isql -Usa -Ppassword -Sserver_name -i $SYBASE_/$SYBASE_ASE/scripts/installmaster
   

   On Windows (from the %SYBASE%\%SYBASE_ASE%\scripts directory):

   isql -Usa -Ppassword -S<server_name> -i instmstr
   

5. If your site added any procedures or made other changes in sybsystemprocs, you must make the same changes in the new sybsystemprocs database.