How to solve sybase ASE log Overflow

How to solve sybase ASE log Overflow

1. Prepare the database environment

-- Create a device disk init name = 'dat1', physname = '/opt/sybase/data/dat1', size = '1m' disk init name = 'log1 ', physname = '/opt/sybase/data/log1', size = '1m' -- the device is too small, extended disk resize name = 'dat1 ', size = '1m '-- create database test on dat1 = '2m' log on log1 = '1m'

2. Insert data until the log is full

use testgocreate table person(name varchar(64)) insert into person values('name')insert into person select * from person--truncate table persondbcc checktable('syslogs')

3. The symptoms are as follows:

1. If the value of free is 7%, the process is in the logsuspend status, and the database log shows the xtasks are sleeping. for space to become available in the log segment for database test2. the Process status is LOGSUSPEND.

 

4. After testing, perform the following operations to solve the problem:

1. If syslogshold has no data, you can directly execute dump transaction test with truncate_only

2. If syslogshold has data, run dump transaction test with truncate_only directly. The following error may be reported:

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

In this case, you can select three methods for processing.

1. Use kill with status_only to monitor the rollback progress.

2. Restart the service

3. Add Devices

Restart Method test:

[root@sybasehost ~]# isql -Usa -P -S ASE12_5_4                                                                                1> shutdown  2> go2 task(s) are sleeping waiting for space to become available in the log segmentfor database test.

The following information appears in the log:

SHUTDOWN is waiting for 1 process(es) to complete.SHUTDOWN is waiting for 1 process(es) to complete.

Hang of the process, then go in from another window, stop with shutdown with nowait, and restart. After the restart, the user database is marked red and the status is suspend.

Execute online database test and report the following error:

 

  SQL Server could not bring database 'test' online.

Run dump transaction test with truncate_only, and then the online database test is successful.

 

Add device mode test

disk init name='log2',physname='/opt/sybase/data/log2',size='1M'alter database test log on log2   ='1M'

Show execution successful

Extending database by 512 pages (1.0 megabytes) on disk log2Warning: using alter database to extend the log segment will cause user thresholds on the log segment within 128 pages of the last chance threshold to be disabled. execution time: 1.471 seconds

The suspended process automatically continues execution.

8192 rows of Space available in the log segment has fallen critically low in database 'test' have been inserted '. all future modifications to this database will be susponded until the log is successfully dumped and space becomes available. the transaction log in database test is almost full. your transaction is being suspended until space is made available in the log. execution time: 158.9 seconds

 

Summary:

 

Logs are suspended and can be processed in three ways.

1. Use kill with status_only to monitor the rollback progress.

2. Restart the service. Remember to use shutdown with nowait. After the restart, You need to truncate the log and then go to the online database.

3. Add Devices

 

View sybase error logs

Install directory directly in the installation directory before version 12.0
Install directory under ASE-12_x directory under sybase installation directory after version 12.0

Sybase ASE 125 usage Problems

Sybooks.sybase.com/nav/base.do

This is all the official documents of SYBASE.
The documents in ASE12.5 contain Chinese characters:
Infocenter.sybase.com/..le.htm