Research on Sybase ASE log Overflow solution

1. Preparing the database environment

--Create Device DISK init name= ' dat1 ', physname= '/opt/sybase/data/dat1 ', size= ' 1M ' disk init name= ' log1 ', physname= '/opt/sybase/ Data/log1 ', size= ' 1M '--device is too small, expand 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) inserts into person values (' name ') inserts into person select * from person-- TRUNCATE TABLE PERSONDBCC checktable (' syslogs ')

3. The following phenomena occur

1.free is 7%, the process appears logsuspend state, the database log appears Xtasks is sleeping. For space to become available in the log segment for database tes T2. Process status is Logsuspend

4. After testing, perform the following actions to solve the problem

1. If Syslogshold has no data, the DUMP transaction test with TRUNCATE_ONLY can be executed directly

2. If the syslogshold has data, execute the DUMP TRANSACTION test with TRUNCATE_ONLY directly and may report the following error

' 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. '

This time you can choose three ways to process

1.kill process, using kill with status_only to monitor rollback progress

2. Restart the service

3. Adding equipment

Restart Mode test:

[[email protected] ~]# isql-usa-p-S Ase12_5_4                                                                                1> shutdown  2> go2 Task (S) is sleeping waiting for space t O 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.

Process hang, then from another window into the shutdown with nowait to stop, and then restart, reboot after the user library marked red, the status of suspend

Execute the online database test with the following error

  SQL Server could not bring database ' test ' online.

Execute DUMP TRANSACTION test with TRUNCATE_ONLY, and then online database test succeeds

Increase Device Mode testing

DISK init name= ' log2 ', physname= '/opt/sybase/data/log2 ', size= ' 1M ' ALTER DATABASE test log on log2   = ' 1M '

Show successful execution

Extending database by + pages (1.0 megabytes) on disk log2warning:using ALTER database to extend the log segment would C Ause user thresholds on the log segment within pages of the last chance threshold to be disabled. Execution time: 1.471 seconds

The original pending process automatically resumes execution

Inserted 8192 lines space available in the log segment have fallen critically low in database ' test '.  All future modifications to this database would be suspended until the log is successfully dumped and space becomes availab Le. The transaction log in database test was almost full.  Your transaction is being suspended until space was made available in the log. Execution time: 158.9 seconds

Summarize:

Log hangs and can be handled in three different ways

1.kill process, using kill with status_only to monitor rollback progress

2. Restart the service, remember to use shutdown with nowait, after reboot to first truncate the log and then online database

3. Adding equipment

Research on Sybase ASE log Overflow solution