Error 4204

Severity

17

Message text

Unable to continue logged version of DUMP TRAN. No space left in database; use NO_LOG option or run ALTER DATABASE. Else, an old transaction may be active; see Syslogshold table.

Explanation

This error occurs when Adaptive Server runs out of space in the transaction log during a dump transaction command.

Action

Use any of the following options to recover from error 4204:

Each option is described below.


Checking for old transactions

In the master database, there is a table called syslogshold. This table contains information about each database’s oldest active transaction (if any) and Replication Server truncation point (if any) for the transaction log. This table is built dynamically when you query it.

Check syslogshold for old transactions for the database for which the error occurred:

1> use master
2> go

1> select * from syslogshold 
2> where dbid = <database_ID>
3> go

Determine whether the oldest active transaction can be terminated (perhaps it was left active unintentionally). If you can terminate this transaction and then execute a dump transaction with no_log command, space may be returned to the database. Continue this process until there are no other old transactions that can be terminated. If, after terminating some old transactions, you still get error 4204, try one of the other options described in this write-up.


Recovering using dump transaction with no_log

  1. Use the following method to determine approximately how many pages the transaction log occupies. Enter the commands that correspond to the version of Adaptive Server Enterprise you are using:

    • Versions earlier than 15.0:

      1> use <database_name>
      2> go
      
      1> select data_pgs(8,doampg)
      2> from sysindexes where id=8
      3> go
      
    • Version 15.0 and later:

      1> select data_pages (<database id>,8)
      2> go
      

    Where “8” is the ID for syslogs. The result is the number of data pages (2K pages on most operating systems) that the transaction log occupies.

    NoteThe query results may be inaccurate by as many as 16 pages, but using the query is much faster than counting the rows in the transaction log.

  2. Dump the transaction log using the no_log option.

  3. Repeat step 1. If the transaction log now occupies significantly fewer pages, continue with the next step now.

    If, however, there are still a large number of pages in the syslogs table, an outstanding transaction is probably preventing the log from being cleared. If this is the case, do one of the following:

    • Follow the instructions in “Detecting and clearing long-running transactions” in the chapter “Other Useful Tasks” in the most recent version of Troubleshooting and Disaster Recovery.

    • Restart Adaptive Server and repeat step 2. When Adaptive Server starts and this database is recovered, the outstanding transaction is rolled back, allowing the log to be cleared by a subsequent dump transaction.

  4. Dump the database now, because the no_log option prevents subsequent changes recorded in the log from being used to recover from a media failure. You must perform a dump database now to ensure the recoverability of subsequent database modifications using transaction logs.

    NoteThis database dump is not required if your backup and recovery procedures do not include saving the transaction logs for media failure recovery.

    If you are concerned that your transaction log is not large enough for your Adaptive Server, refer to information about estimating the transaction log size within “Creating and Managing User Databases” in the System Administration Guide: Volume 2 before deciding to increase its size. If you choose to increase the size, refer to Reference Manual: Commands for more information about the alter database command used to increase log size. Remember that once they have been increased, log and database sizes cannot be decreased.


Extending the transaction log segment

WARNING! If you use this option to extend the transaction log segment, you will not be able to get that space back later.

  1. Refer to “Estimating the Transaction Log Size” in the chapter “Creating and Managing User Databases” in the System Administration Guide: Volume 2 before deciding whether to increase the transaction log size.

  2. To get information about the log segment, enter commands like the following example and replace “test” with the name of the database in the error message:

    1> use test
    2> go
    
    1> sp_helpsegment logsegment
    2> go
    
    segment  name                            status
    -------  ------------------------------  ------
    2        logsegment                      0
    device                      size         free_pages
    --------------------------- ------------ ----------
    dev1                        1.0MB        512
    
    table_name                  index_name   indid
    --------------------------- ------------ ------
    syslogs                     syslogs      0
    
    
  3. If you have more space in the current log device fragment, increase the amount of space allocated for the transaction log with commands like the following:

    1> use master
    2> go
    
    1> alter database <database_name>
    2> log on <device_name> = <additional_space>
    3> go
    

    Where:

    • <database_name> is the name of the database for which the log device fragment is to be extended.

    • <device_name> is the name of the database device on which to locate the database extension.

    • <additional_space> is the number of megabytes to extend the log device fragment.

  4. If you do not have any more space in the current log segment, you can use sp_extendsegment to extend the range of the log segment to another database device (the database must already exist on that device fragment before you run these commands):

    1> use <database_name>
    2> go
    
    1> sp_extendsegment logsegment, <database_name>,
    2> <device_name>
    3> go
    

    Where:

    • <database_name> is the name of the database where the segment is to be extended.

    • <device_name> is the name of the additional database device for the log segment.

      WARNING! If you use sp_extendsegment to extend the range of the log segment to another database device, and the other device contains data, the segment on which that data exists will become a log segment. This situation can cause you to run out of space in the database earlier than expected and may cause other problems, including 2558 errors.

Versions in which this error is raised

All versions