How to solve sybase ASE log Overflow

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.