Reset tempdb to Default Size

SAP ASE must be in single-user mode to prevent another user from altering the database while you manually update sysusages.

Warning!   Use this procedure only on tempdb. It works because tempdb is rebuilt each time the system is shut down and restarted. Using this procedure on any other database results in database corruption.
  1. Increase tempdb to 4MB on master.
  2. Log in to SAP ASE as the system administrator:
  3. In case something goes wrong and you need to restore from backup, dump the master database.
    dump database master to "dump_device"

    where dump_device is the name of the target dump device.

  4. To aid in master database recovery, if necessary, use the bcp...out command to save the following key system tables to data files
    • master..sysusages

    • master..sysdevices

    • master..sysdatabases

    • master..syslogins

    • master..sysconfigures

    • master..syscharsets

    • master..sysloginroles

    • master..sysservers

    • master..sysremotelogins

    • master..sysresourcelimits

    • master..systimeranges

  5. From the master database, reconfigure SAP ASE to allow changes to the system catalog:
    sp_configure "allow updates", 1
  6. Display the current rows belonging to tempdb from sysusages, and note the number of rows affected:
    begin transaction
    select * from sysusages
    where dbid = db_id('tempdb')

    The db_id function returns the database ID number. In this case, the database ID for tempdb is returned.

  7. Set the first 2MB of tempdb back to data and log in case they were separated:
    update sysusages
    set segmap = 7 where dbid = db_id('tempdb')
    and lstart = 0
  8. Delete all other rows belonging to tempdb from sysusages.The number of rows affected should be one less than the number of rows affected by the previous select command.
    delete sysusages where dbid = db_id('tempdb')
    and lstart != 0
    Warning!   Each time SAP ASE is shut down and restarted, the model database is copied to tempdb. Therefore, if the model database has been increased beyond its default size, do not reduce the size of tempdb so that it is smaller than model.
  9. Verify that tempdb has one entry that looks like this:
    select * from sysusages
    where dbid = db_id('tempdb')
  10. If the information is correct, go to step 10 to commit the transaction.
    If you see a problem, back out of your changes by entering the following commands:
    rollback transaction

    Do not continue with the procedure. Review the steps you performed to determine the cause of the problem.

  11. Complete the transaction:
    commit transaction
  12. Reconfigure SAP ASE to disallow changes to the system catalog (the normal state for SAP ASE):
    sp_configure "allow updates", 0
  13. Immediately issue a checkpoint and shut down SAP ASE:
    Warning!   You must shut down SAP ASE before altering the size of tempdb again. If you continue to run without shutting down and restarting, you will receive serious errors on tempdb.
    checkpoint
    go
    shutdown
    go
  14. Restart SAP ASE.