troubleshooting Question

enhance sybase tempdb structure.

Avatar of marrowyung
marrowyung asked on
DatabasesSybase DatabaseStorageSAP
17 Comments6 Solutions247 ViewsLast Modified:

I am new to Sybase and I have a operation to optimize the tempdB by creating more than one tempdb files and move to different disk, please let me know if the steps are correct and command are correct :

: 1.Created 4*250GB LUN for tempdb
2.Created 4 DG (data group in SAN for creating LUN) used for tempdb device
LUN - TempDB ======> Used for tempdb
LUN - TempDB_large ======> Used for tempdb_large
LUN - TempDB_online1 ======> Used for tempdb_online1
LUN - TempDB_online2 ======> Used for tempdb_online2
3. Dedicated Raw device for tempdb (70 GB Data, 30 GB): tempdev_data1 , tempdev_log1, tempdev_online_data1 , tempdev_online_log1, tempdev_large_data1 , tempdev_large_log1...etc.
4.Current Status-
tempdb 37504.0 MB Mixed
tempdb2 500.0 MB Mixed
tempdb3 30048.0 MB Mixed
tempdb4 47600.0 MB Mixed
we need to shrink / drop and make each tempdb 25000 MB with data and log separated
CREATE TEMPORARY DATABASE tempdb_large ON tempdev_large_data1 = '20000M' LOG ON tempdev_large_log1 = '5000M'
CREATE TEMPORARY DATABASE tempdb_online1 ON tempdev_online_data1 = '20000M' LOG ON tempdev_online_log1 = '5000M'
CREATE TEMPORARY DATABASE tempdb_online2 ON tempdev_online_data2 = '20000M' LOG ON tempdev_online_log2 = '5000M'
exec master.dbo.sp_dboption tempdb, 'select into/bulkcopy/pllsort', true
exec master.dbo.sp_dboption tempdb, 'trunc log on chkpt', true
exec master.dbo.sp_dboption tempdb, 'allow wide dol rows', true
exec master.dbo.sp_dboption tempdb, 'abort tran on log full', true
    is it good to setup each tempDB using these 4 x option, is it necessary ?

exec master.dbo.sp_dboption tempdb_large, 'select into/bulkcopy/pllsort', true
exec master.dbo.sp_dboption tempdb_large, 'trunc log on chkpt', true
exec master.dbo.sp_dboption tempdb_large, 'allow wide dol rows', true
exec master.dbo.sp_dboption tempdb_large, 'abort tran on log full', true
exec master.dbo.sp_dboption tempdb_online1, 'select into/bulkcopy/pllsort', true
exec master.dbo.sp_dboption tempdb_online1, 'trunc log on chkpt', true
exec master.dbo.sp_dboption tempdb_online1, 'allow wide dol rows', true
exec master.dbo.sp_dboption tempdb_online1, 'abort tran on log full', true
exec master.dbo.sp_dboption tempdb_online2, 'select into/bulkcopy/pllsort', true
exec master.dbo.sp_dboption tempdb_online2, 'trunc log on chkpt', true
exec master.dbo.sp_dboption tempdb_online2, 'allow wide dol rows', true
exec master.dbo.sp_dboption tempdb_online2, 'abort tran on log full', true
5. Create new defined cache
Add memory - sp_configure 'max memory',23600000
tempdb will continue use of default data cache ( 12500.0 MB). There will be three more named cache with 5GB for each tempdb

In Sybase, is it a correct way to enlarge tempdb and at the same time, adjust msx memory using sp_configure 'max memory' ?
Define new data cache -
sp_cacheconfig 'tempdb_large_cache', "5000M", mixed, strict, "cache_partition=64"
sp_poolconfig tempdb_large_cache, "3000M", "4K", "2K"
sp_poolconfig tempdb_large_cache, "800M", "16K", "2K"
is it a good way to setup data cache for Sybase temp in this way?

sp_cacheconfig 'tempdb_online1_cache', "5000M", mixed, strict, "cache_partition=64"
sp_poolconfig tempdb_online1_cache, "3000M", "4K", "2K"
sp_poolconfig tempdb_online1_cache, "800M", "16K", "2K"
sp_cacheconfig 'tempdb_online2_cache', "5000M", mixed, strict, "cache_partition=64"
sp_poolconfig tempdb_online2_cache, "3000M", "4K", "2K"
sp_poolconfig tempdb_online2_cache, "800M", "16K", "2K"
Bind newly created data cache -
sp_bindcache tempdb_large_cache, tempdb_large
sp_bindcache tempdb_online1_cache, tempdb_online1
sp_bindcache tempdb_online2_cache, tempdb_online2

correct way to map a tempdb file to a tempdb cache ? is it a MUST to map a cache to EACH tempDB file ?
6. Group - creation & mapping
sp_tempdb add, tempdb, "default"
sp_tempdb 'create', 'tempdb_large'
sp_tempdb 'create', 'tempdb_online_grp'
sp_tempdb add, tempdb_large , "tempdb_large"
sp_tempdb add, tempdb_online1, "tempdb_online_grp"
sp_tempdb add, tempdb_online2, "tempdb_online_grp"
Bind application id to tempdb_large group - happsrv , asysmz7 , asys454 & asysvxt

usually do we need to create tempdb group and map to tempdb file ? why need to keep mapping for tempdb group, cache and file ?
7. Housekeeping
Remove current tempdb devices
Release raw device and LUN associated with them
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 6 Answers and 17 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 6 Answers and 17 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros