[ Home  |  FAQ-Related Q&As  |  General Q&As  |  Answered Questions ]

    Search the Q&A Archives

...reduce my tempdb size once i have increased it by...

<< Back to: Sybase FAQ: 9/19 - ASE Admin (6 of 7)

Question by rajs
Submitted on 12/23/2003
Related FAQ: Sybase FAQ: 9/19 - ASE Admin (6 of 7)
Rating: Rate this question: Vote
How can i reduce my tempdb size once i have increased it by creating a device also?


Answer by Mayank123
Submitted on 2/21/2006
Rating: Not yet rated Rate this answer: Vote
Reduce the Size of tempdb: Reset tempdb to Default Size:

1.   Start Adaptive Server in single-user mode to prevent another user from altering the database while you are manually updating sysusages

Note: Since there is only one user in PEM sa so we do not need this but you will have to remove your network cable from PEM so that no changed occur in database intiated by you corba interface.

2.   Login to isql :

isql -Usa -Sserver_name –Ppassword

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

Dump will be save in c:\winnt/system32 by default. It will show in warning.

4.   Save the following key system tables to data files with the bcp..out command, to aid in master database recovery if necessary:

o   master..sysusages
o   master..sysdevices
o   master..sysdatabases
o   master..syslogins
o   master..sysconfigures
o   master..syscharsets
o   master..sysloginroles
o   master..sysservers
o   master..sysremotelogins
o   master..sysresourcelimits
o   master..systimeranges


5.   Reconfigure Adaptive Server to allow changes to the system catalog:
1> use master
2> go
1> sp_configure "allow updates", 1
2> go

6.   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 where dbid = db_id('tempdb')
2> go

7.   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') and lstart = 0
3> go

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.
1> delete sysusages where dbid = db_id('tempdb') and lstart != 0
3> go


Note: Once again see output of sp_helpdb to check that you model database has 2mb size only.

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

Note: if output is not similar to as show above rollback :
1.   1> rollback transaction
2.   2> go

Othewise continue.

  10.  Complete the transaction:
1> commit transaction
2> go
11.  Reconfigure Adaptive Server to disallow changes to the system catalog (the normal state for Adaptive Server):
1> sp_configure "allow updates", 0
2> go

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

12. Restart Adaptive Server.
Verify and Alter tempdb on Desired Devices

1.   Log into Adaptive Server as the System Administrator:
% isql -Usa -Sserver_name -Ppassword
2.   Verify that tempdb has one 2MB fragment for data and log on the master device:
1> sp_helpdb tempdb
2> go
3.   Alter tempdb as required to extend the database onto the desired devices. For example:
1>   alter database tempdb
2> on device_name = device_size
3> go
device_size is specified in megabytes.
4.   Back up the master database again, in case you need to restore from this point:
5.   1> dump database master to "dump_device"
2> go
where dump_device is the name of the target dump device


The first 2MB of tempdb must remain on the master device, but future log space allocations will be made on the device specified by sp_logdevice.

 

Your answer will be published for anyone to see and rate.  Your answer will not be displayed immediately.  If you'd like to get expert points and benefit from positive ratings, please create a new account or login into an existing account below.


Your name or nickname:
If you'd like to create a new account or access your existing account, put in your password here:
Your answer:

FAQS.ORG reserves the right to edit your answer as to improve its clarity.  By submitting your answer you authorize FAQS.ORG to publish your answer on the WWW without any restrictions. You agree to hold harmless and indemnify FAQS.ORG against any claims, costs, or damages resulting from publishing your answer.

 

FAQS.ORG makes no guarantees as to the accuracy of the posts. Each post is the personal opinion of the poster. These posts are not intended to substitute for medical, tax, legal, investment, accounting, or other professional advice. FAQS.ORG does not endorse any opinion or any product or service mentioned mentioned in these posts.

 

<< Back to: Sybase FAQ: 9/19 - ASE Admin (6 of 7)

[ Home  |  FAQ-Related Q&As  |  General Q&As  |  Answered Questions ]
© 2008 FAQS.ORG. All rights reserved.