Hi all,

I am trying to enter new data into an empty Sybase ASE 12 database. Anytime I modify the database, however, I get the following error:

Server Message: Number 7415, Severity 10
Line 1:
The transaction log in database ActivityMonitoring is almost full. Your transaction is being suspended until space is made available in the log.

Where 'ActivityMonitoring' is the name of my database. I have tried many things, namely:

1. dump tran with no_log ---- Yielding the following error:

DUMP TRANSACTION for database 'ActivityMonitoring' could not truncate the log. Either extend the log using ALTER DATABASE ... LOG ON command or eliminate the oldest active transaction in database 'ActivityMonitoring' shown in syslogshold table.

2. dump tran with (I forget the other one: truncate, or something) ---- Yielding the same error as above

3. select lct_admin("unsuspend", db_id("ActivityMonitoring")) ----
This yields:

lct_admin(unsuspend): This command is not supported anymore. If you want to abort the tasks sleeping on log-suspend state, please consider using lct_admin(abort).
(1 row affected)

....and opens an empty table which has one record, in one unnamed column, with a value of 0

4. So, I tried the abort method:
select lct_admin("abort", db_id("ActivityMonitoring")) ---- Yielding:

lct_admin(abort): Process 4 is not an user process. Only an user process waiting on log-suspend mode can be aborted.
(1 row affected)

....and opens the empty table described above

5. I have got nothing in syslogshold:
select l.spid,
db_name(l.dbid) "db name",
name "proc name",
hostname,
starttime
from master..syslogshold l, master..sysprocesses p
where l.spid=p.spid
order by starttime

Yields: Empty table (0 rows affected)

I have set the dboptions to trunc, true ....

Help me, please!! I just cannot win...

Thanks in advance,
Pokksey

hello,

just a try to ur problem,

check for the size of log....is it default size ??
secondly, if log size is not default...u can increase the log size or use alter database with log on option , once the log is increased ..dump tran .. and if u like u can drop the added log segment so log size will again come back to previous one.

Pooja.

Hi,

Try this....

1. Check your log device & database device whether they are using same physical db device
separate log device from data device.
2. Checkpoint the database manually & then try to dump the log.

Thanks for the reply ppl,

Unfortunately, I have no experience in database administration at all, so I'll try your suggestions with help from the manual. If you could give me some explicit pointers on how to go about separating/checking log and data devices I would be extremely grateful. Thanks...

1. Run procedure sp_helpdb 'name of db'
2. Check device name,size, last column will show something like 'data only',log only','data & log'
3. If a device is showing both data & log. It means data & log are residing on the same device try to separate it.
4. Create another database device by using disk init.
5. Run stored procedure sp_logdevice 'dbname',device name'

for eg. if u have db name say accounts & it's data & log are residing on device dev1. Then inorder to move log create another db device 'dev2'
use sp_logdevice 'accounts','dev2'. then dev2 will become logdevice.

Hi
Try to gothrough this FAQ .may be helpful for you.
Once your problem is solved please post it how you solved it, as it can be helpful for others..

http://www.isug.com/Sybase_FAQ/ASE/s...1.4.html#1.4.2

Thanks & Regards
Is

Hi,

I have created the new device - but when I try to run sp_logdevice it comes up with the following error:

"The specified device is not used by the database"

What the?? I thought sp_logdevice is precisely for this reason: to make the specified database USE the device...

Is there a step I am missing? Do I need to explicitly state, somehow, that my database now will USE the new device? And then I can run sp_logdevice? Most puzzling...

Also, when I run a sp_helpdb on my database, its size is 2.0M, its usage is 'data and log', and free kbytes is only 80.... Is this bad?? Can anyone please help me further. Thanks a lot for your replies...

Thanks,
Poks

Okay,

It seems that everything has turned out okay - I created a 2nd device, used alter database to set the new device to my database and then sp_logdevice.

Next, I had a problem with the space on my database (It was only 2M). So I created a segment on my master data device and gave it 20M of space. At least, I think that's what I did. But it is now accepting data.

I assume that when I run out of space on the master segment I will need to create another segment with an allocated amount of space? Is 20M enough (for a small palm app with 6 tables/30 fields@50 records in each table a day: all cols varchar(255)) ? Is this a naive question?

Just one more query regarding rowsize. I have used varchar(255) for most of my columns and got a warning when I created the columns about the rowsize not being large enough. Should I change the rowsize of each column? How? And will the current setup truncate my data if it exceeds the default rowsize?

Thanks for the help...
Poks

Hi Poks,

I'm beginning to form a picture of what you did. It sounds like you created a database as follows:

create database ActivityMonitoring
go

This creates a database on the "default devices" and the default size. After installing ASE the master device has the attribute of "default device". The best thing to do is prevent any databases to be accidentally created on the master device is to turn off the attribute:

exec sp_diskdefault, 'master', defaultoff
go

If you have no default devices then ASE will complain when you create a database, asking you to specify which devices the database will be created on. This is good because you're now in charge of the environment.

You are correct to be concerned the space on the master device. The master database cannot be moved from the master device or extended on to another device. At the moment that's probably OK. but after few logins etc have been added the master database will begin fill up, especially if its the default size, 6MB.

The best thing to do recreate the ActivityMonitoring database on it's own devices or on specific data and log devices, away from the master device.

You're going to need some downtime to do this. So stop the application while you're making the changes.

Obtain the script used to create the database, including the alterations you have just made. Store this in a safe place. Also dump the database so that it can be reloaded later.

Create two new devices; data_01 and log_01. These need to be large enough to contain both the data and log parts of the database. Create these devices using disk init in the same manner as you recently created the log device.

Something like this (assuming winnt as the OS) will suffice

disk init
name = "data_01",
physname = "c:\devices\data_01.dev"
size = 51200
go

disk init
name = "log_01",
physname = "c:\devices\log_01.dev"
size = 51200
go

Once done, create a new database on these devices, remember the name of the database must be changed in the script because you can't have two databases with the same name.

Your database creation script will need to look something like this, but exact script will depend on the sizes and segment maps used when creating the database in the first place.

create database ActivityMonitoring_New on data_01
go
alter database ActivityMonitoring_New on log_01 = Size of log you specified
go
alter database ActivityMonitoring_new on data_01 = 20
go

Load the dump of the database into the newly created database.

Run sp_helpdb ActivityMonitoring and sp_helpdb ActivityMonitoring_New and check the segment maps are the same. The segment map is the allocation of default, system and logsegments within the database.

You now need to rename the databases so the application doesn't pick up the old database.

The following renames ActivityMonitoring to ActivityMonitoring_old. You'll need to do something similar to change ActivityMonitoring_New to ActivityMonitoring.

use master
go
sp_dboption ActivityMonitoring, 'single', true
go
use ActivityMonitoring
go
checkpoint
go
sp_renamedb ActivityMonitoring, ActivityMonitoring_old
go
use master
go
sp_dboption ActivityMonitoring, 'single', true
go
use ActivityMonitoring
go
checkpoint
go

If everything went OK the old database should have _old on the end of it.

When you run out of space you will need to allocate more space. But don't allocate it on the master device. It makes recovery very difficult if the master device dies.

20 MB should be sufficient for you data as your specifications add up to 2,295,000. In real life this will be larger, but so that you would need to worry about it.

The warning you received was probably something along the lines "The row size may exceed the page size". This is only going to be a problem if you have a single row that is longer than 1960 bytes. The length of the data in the row will be slightly shorter because of the overheads to mange a row. If a row length greater than 1960 bytes is applied the insert or update statement will fail with an error message indicating the row length was too large.

Personally I would reconsider the data model and design the database with a fewer number of varchar columns. You may also be able to take advantage of smaller indexes and improve the performance of the application.

Let us know how you get on.

Richard.