SAP Sybase ASE 15.7 deleting devices sysusages sysdevices to decrease tempdb database size




Caution getting this wrong can render a sybase ASE dataserver unusable!


In the illustration below the tempdb size is decreased by dropping unwanted tempdb database fragments.

Step 1 Documenting the fragments


select
db_name(su.dbid) +
" | Device_Total_MB=" + convert(varchar(10),(sd.high-sd.low+1)/512) + 
" | lstart=" + convert(varchar(10), su.lstart) +
" | DB_Fragment_MB=" + convert(varchar(10), @@maxpagesize*su.size/1048576, sd.vdevno) + 
" | dev_name=" + sd.name + 
" | " + convert(varchar(100), sd.phyname)
from sysdevices sd,  sysusages su
where su.vdevno = sd.vdevno
and sd.cntrltype = 0
and su.dbid = db_id("tempdb")
order by su.lstart


 tempdb | Device_Total_MB=73 | lstart=0 | DB_Fragment_MB=6 | dev_name=master | /home/sybase/SYB157/data/master.dat                                                                                                                                                                                                                                                                                                                                                                           
 tempdb | Device_Total_MB=100 | lstart=1536 | DB_Fragment_MB=100 | dev_name=tempdbdev | /home/sybase/SYB157/data/tempdbdev.dat                                                                                                                                                                                                                                                                                                                                                               
 tempdb | Device_Total_MB=20 | lstart=27136 | DB_Fragment_MB=20 | dev_name=tempdb_tmp1 | /home/sybase/SYB157/data/tempdb_tmp1.dat                                                                                                                                                                                                                                                                                                                                                            
 tempdb | Device_Total_MB=200 | lstart=32256 | DB_Fragment_MB=200 | dev_name=tempdb_tmp2 | /home/sybase/SYB157/data/tempdb_tmp2.dat                


Step 2 dump the master database



Dump the master database:

dump database master to "/home/sybase/dumps/master.dmp.2016_11_15"

1> dump database master to "/home/sybase/dumps/master.dmp.2016_11_15"
2> go
WARNING: In order to LOAD the master database, the ASE must run in single-user mode. If the master database dump uses multiple volumes, you must execute sp_volchanged on another ASE at LOAD time in order to signal volume changes.
Backup Server: 4.171.1.1: The current value of 'reserved pages threshold' is 85%.
Backup Server: 4.171.1.2: The current value of 'allocated pages threshold' is 40%.
Backup Server: 4.171.1.5: The current value of 'parallel scan' is 2.
Backup Server session id is: 56. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 4.41.1.1: Creating new disk file /home/sybase/dumps/master.dmp.2016_11_15.
Backup Server: 6.28.1.1: Dumpfile name 'master163200BCA4 ' section number 1 mounted on disk file '/home/sybase/dumps/master.dmp.2016_11_15'
Backup Server: 4.188.1.1: Database master: 2636 kilobytes (17%) DUMPED.
Backup Server: 4.188.1.1: Database master: 10642 kilobytes (100%) DUMPED.
Backup Server: 3.43.1.1: Dump phase number 1 completed.
Backup Server: 3.43.1.1: Dump phase number 2 completed.
Backup Server: 3.43.1.1: Dump phase number 3 completed.
Backup Server: 4.188.1.1: Database master: 10652 kilobytes (100%) DUMPED.
Backup Server: 3.42.1.1: DUMP is complete (database master).

Step 3 bcp out sysdatabases, sysdevices and sysusages

bcp out these system tables at the operating system command line:
bcp master.dbo.sysdatabases out sysdatabases.bcp -Usa -SSYBSERVER  -c -t'|'
bcp master.dbo.sysdevices out sysdevices.bcp -Usa -SSYBSERVER  -c -t'|'
bcp master.dbo.sysusages out sysusages.bcp -Usa -SSYBSERVER  -c -t'|'
run sp_configure to "allow updates"
1> sp_configure "allow updates", 1
2> go



Step 4 remove selected rows from sysusages and sysdevices


list the fragments for removal (total 3):
1> select * from sysusages where dbid = 2 and vdevno != 0
2> go
 dbid   segmap      lstart     size       vstart      location unreservedpgs crdate                          vdevno
 ------ ----------- ---------- ---------- ----------- -------- ------------- ------------------------------- -----------
      2           7       1536      25600           0        0         25500             Aug  5 2013  6:19PM           3
      2           7      27136       5120           0        0          5100             Nov 13 2016  5:41PM          23
      2           7      32256      51200           0        0         51000             Nov 13 2016  5:50PM          24


1> begin tran
2> go
1> delete from sysusages where dbid = 2 and vdevno != 0
2> go
(3 rows affected)
1> commit tran
2> go
1> select * from sysusages where dbid = 2 and vdevno != 0
2> go
 dbid   segmap      lstart     size       vstart      location unreservedpgs crdate                          vdevno
 ------ ----------- ---------- ---------- ----------- -------- ------------- ------------------------------- -----------

(0 rows affected)
1>

If for some reason there were not 3 rows affected "rollback tran" instead of "commit tran".
1> select name, phyname from sysdevices where name in ("tempdbdev","tempdb_tmp1","tempdb_tmp2")
2> go
 name
         phyname                                                                                                                                               
 ------------------------------------------------------------
         --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 tempdb_tmp1
         /home/sybase/SYB157/data/tempdb_tmp1.dat                                                                                                              
 tempdb_tmp2
         /home/sybase/SYB157/data/tempdb_tmp2.dat                                                                                                              
 tempdbdev
         /home/sybase/SYB157/data/tempdbdev.dat     


1> begin tran
2> go
1> delete from sysdevices where name in ("tempdbdev","tempdb_tmp1","tempdb_tmp2")
2> go
(3 rows affected)
1> commit tran

shutdown the dataserver
1> shutdown
2> go

remove the old device files:
rm /home/sybase/SYB157/data/tempdb_tmp1.dat
rm /home/sybase/SYB157/data/tempdb_tmp2.dat
rm /home/sybase/SYB157/data/tempdbdev.dat

restart sybase dataserver.

change back "allow updates":
1> sp_configure "allow updates", 0
2> go

Step 5 add new devices to tempdb as required


After running the above steps the tempdb database now is 6 MB in size with only a fragment on master.

As the motivation for removal of devices is likely to be to reconfigure tempdb, new devices will be added to tempdb.

Example adding 900 Mb to tempdb:
1> disk init name="tempdb_tmp101", size="900M",
2> physname="/var/apps/devices/tempdb_tmp101.dat" , dsync="false", directio="false"

1> alter database tempdb on tempdb_tmp101=900
2> go
Extending database by 230400 pages (900.0 megabytes) on disk tempdb_tmp101

Disclaimer


This page is intended to show a worked illustration of reducing tempdb size.

There is no implication that this is a SAP sybase supported procedure.
No liability can be accepted for any loss due to any information found on this page.



Alastair Cook email:
cookalastair @ btinternet.com

Page created - 15th Nov 2016


Push Pull Door