Before proceeding, start Adaptive Server in single-user mode to prevent another user from altering the database while you are manually updating sysusages. Refer to “How to Start Adaptive Server in Single-User Mode” for instructions on doing this.
Log into Adaptive Server as the System Administrator:
% isql -Usa -Sserver_name -Ppassword
Dump the master database in case something goes wrong and you need to restore from the backup:
1> dump database master 2> to "dump_device" 3> go
where dump_device is the name of the target dump device.
Save the following key system tables to data files with the bcp..out command, to aid in master database recovery if necessary:
master..sysusages
master..sysdevices
master..sysdatabases
master..syslogins
master..sysconfigures
master..syscharsets
master..sysloginroles
master..sysservers
master..sysremotelogins
master..sysresourcelimits
master..systimeranges
The syntax for saving the tables to files appears in “Copy the System Tables to Files”.
WARNING! This procedure should be used 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 will result in database corruption.
Reconfigure Adaptive Server to allow changes to the system catalog:
1> use master 2> go
1> sp_configure "allow updates", 1 2> go
Display the current rows belonging to tempdb from sysusages, and note the number of rows affected:
1> begin transaction 2> go
1> select * from sysusages
2> where dbid = db_id('tempdb')
3> go
The db_id function returns the database ID number. In this case, the database ID for tempdb is returned.
Set the first 2MB of tempdb back to data and log in case they were separated:
1> update sysusages
2> set segmap = 7 where dbid = db_id('tempdb')
3> and lstart = 0
4> go
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.
1> delete sysusages where dbid = db_id('tempdb')
2> and lstart != 0
3> go
WARNING! Each time Adaptive Server 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.
Verify that tempdb has one entry that looks like this:
1> select * from sysusages
2> where dbid = db_id('tempdb')
dbid segmap lstart size vstart --- ------ ----- ---- ------ 2 7 0 1024 2564
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:
1> rollback transaction 2> go
Do not continue with the procedure. Review the steps you performed to determine the cause of the problem.
Complete the transaction:
1> commit transaction 2> go
Reconfigure Adaptive Server to disallow changes to the system catalog (the normal state for Adaptive Server):
1> sp_configure "allow updates", 0 2> go
Immediately issue a checkpoint and shut down Adaptive Server:
WARNING! You must shut down Adaptive Server before altering the size of tempdb again. If you continue to run without shutting down and restarting, you will receive serious errors on tempdb.
1> checkpoint 2> go
1> shutdown 2> go
Restart Adaptive Server.
