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.
One thought on “Sybase: Extending the transaction log”