Before proceeding, start Adaptive Server in single-user mode to prevent another user from altering the database while you are manually updating sysusages.
Increase tempdb to 4mb on master.
Log into Adaptive Server as the System Administrator:
Dump the master database in case something goes wrong and you need to restore from the backup:
dump database master to "dump_device"
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
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.
From the master database, reconfigure Adaptive Server to allow changes to the system catalog:
sp_configure "allow updates", 1
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.
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
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 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:
select * from sysusages
where dbid = db_id('tempdb')
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.
Complete the transaction:
commit transaction
Reconfigure Adaptive Server to disallow changes to the system catalog (the normal state for Adaptive Server):
sp_configure "allow updates", 0
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.
checkpoint go shutdown go
Restart Adaptive Server.
