This will show you how to extend the transaction log on a user database by adding an additional log device. In many cases dumping the transaction log or increasing the size of the existing device would be better but in some occasions, I’ve needed this…

First you have to create a new device on the hard disk using “disk init”:

use master
go
disk init name = "log_2_dev", physname = "/db_data/devices/log_2_dev", size=204800
go

In this example, “log_2_dev” is the name of the new device in ASE. It will be created in the file “/db_data/devices/log_2_dev”. And its size will be 400 MB (204800 pages, 2KB each). Instead of specifying a size in 2K-blocks you can also write 400M to directly define a size in megabytes.

You can check whether the device has been properly created like this:

sp_helpdevice
go

Then you need to assign this device to the transaction log of your database (assuming the name of the database is mydb):

use master
go
alter database mydb log on log_2_dev = '400M'
go

It tells Sybase to additionally use the new device for the transaction log (using 400 MB).

You can check with the following whether everything went fine:

sp_helpdb mydb
go

You should see two log devices e.g.:

1> sp_helpdb mydb
2> go
 name                     db_size       owner                    dbid
         created
         status
 ------------------------ ------------- ------------------------ ------
         --------------
         ------------------------------------------------------------------------------------------------------
 mydb                         1797.0 MB sa                            4
         Jan 24, 2012
         select into/bulkcopy/pllsort

(1 row affected)
 device_fragments               size          usage
         created             free kbytes
 ------------------------------ ------------- --------------------
         ------------------- ----------------
 data_1_dev                          991.0 MB data only
         Jan 24 2012 11:33PM           809514
 log_1_dev                           109.0 MB log only
         Jan 24 2012 11:33PM not applicable
 index_1_dev                         297.0 MB data only
         Jan 24 2012 11:34PM           243404
log_2_dev                           400.0 MB log only
         Jan 25 2012  3:57AM not applicable

 --------------------------------------------------------------
 log only free kbytes = 292174
(return status = 0)

After that you shouldn’t have problems anymore with the transaction log getting full too quickly.

If you need to remove the log device later on, please use the following:

use master
go
sp_dboption mydb, "single user", true
go
use mydb
go
sp_dropsegment 'logsegment','mydb','log_2_dev'
go
use master
go
sp_configure 'allow updates', 1
go
delete from sysusages where dbid=db_id('mydb') and vstart=(select low from sysdevices where name='log_2_dev')
go
sp_configure 'allow updates', 0
go
sp_dropdevice 'log_2_dev'
go
sp_dboption mydb, "single user", false
go

The sing user mode means that no other connection than the one you’re working with will be available. This ensures that no other activities are done in parallel.
Allow updates allows you to modify system tables manually.