Create a Dynamically Growing Sybase Database?

Question

I am building a little installation/packaging script that deploys various database schema items and procedures. Right now, I just use this to make the database:

CREATE DATABASE dbname GO

However, when i point my little package at my larger databases, they quickly fill up the allotted space.

I am not clear how to create a database that has space to grow on sybase....is there some sort of parameter CREATE DATABASE parameter that allows it room to grow, or do I have to supply a specific size per database?

score 1 · Answer 1 · answered Nov 12 '13 at 17:48

Some research has prooved that making a growing one is a bad idea... I ended up doing something like this (for the benefit of those who are looking, i had a hard time figuring out the device part..)

-- Data Device
disk init
name              = 'do02_data',
physname        = 'C:\sybase\data\do02data.dat',
size        = '15G',
directio    = true,
skip_alloc  = true
go

-- Log Device
disk init
name              = 'do02_log',
physname        = 'C:\sybase\data\do02log.dat',
size        = '7G',
directio    = true,
skip_alloc  = true
CREATE DATABASE do02 on do02_data = '15G' LOG ON  do02_log = '7G'             -- will     take some time
GO
sp_dboption do02, 'select into/bulkcopy', true
GO

The 'select into/bulkcopy' was also needed if you're going to use BCP right afterwards.

I just put instructions in my package for the end user to modify these values as needed.

score 0 · Answer 2 · answered Nov 13 '13 at 14:43

Just so it's clear, there are two different ways to create a device.

The default method grabs (allocates) all the space assigned to the device at the time the device is created. So creating a 100Gb device will create a 100Gb file in the filesystem

The other method grabs (allocates) only the space it needs, but will continue to grab space until it reaches the limit specified in the disk init. This option is only available for Unix devices and Windows raw systems, and is specified using skip_alloc = true

Using skip_alloc does not create a dynamically growing database, it only delays allocation until the space is needed. The database will still not grow larger than the space you have assigned it.

If you want to create an automatically expanding database, Sybase has precedures on using sp_dbextends do this.

Users with older versions of Sybase can mimic some of this using thresholds (sp_addthreshold, sp_modifythreshold, sp_dropthreshold).

Create a Dynamically Growing Sybase Database?

2 Answers2