Enlarging your Sybase database

If you're running out of space, you can remove records from the syslog table or you can add space from the master device, says Dr. Mich Talebzadeh, SearchOpenSource.com's Sybase expert. In this tip, Dr. Talebzadeh gives helpful examples of removing unwanted transactions and offers code for boosting your master database capacity.

Removing unwanted transactions

When it is created, the master database has a mixed data and log on the master device. The transaction log is the syslogs table you are referring to, as shown in the example below:

  • sp_helpdb master
  • go

    • name db_size owner dbid created status
    master 100.0 MB sa 1 Jan 15, 2006 mixed log and data

    (1 row affected)

    device_fragments size usage created free kbytes
    master 52.0 MB data and log Jan 15 2006 1:53AM 36912
    master 48.0 MB data and log Jan 15 2006 1:53AM

      Requires Free Membership to View

      Login

    		48960

Syslogs is not a normal table. To get rid of an unwanted transaction in the master database, you can do the following:

dump transaction master with truncate_only
go

Increasing master device space

Alternately, you can increase the size of the master database on master device if you have free space in master device:

  1. sp_helpdevice master
  2. go
device_name physical_name                     
description

    3. master 
 /data2/lon_gen_sql_tst4/master.dat special
 dsync on
 directio off
 default disk
 physical disk
 256.00 MB
 Free: 120.00 MB

So, in the above example, I have 120MB free on the master device. If I want to expand the master database on the master device by 10MB, I can simply do the following:

  1. alter database master on master = 10
  2. go
    Extending database by 1280 pages (10.0 megabytes) on disk master

This was first published in August 2006

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top