17
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.
This error occurs when Adaptive Server runs out of space in the transaction log during a dump transaction command.
Use any of the following options to recover from error 4204:
If any old transactions are active, determine whether they can be terminated. If such transactions do exist and they can be terminated, dump the transaction log, which may free up database space.
Perform a dump transaction with the no_log option. This command removes the inactive part of the log without making a backup copy and without recording the procedure in the transaction log (performs a checkpoint).
Extend the transaction log segment.
Each option is described below.
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.
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.
The 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.
Dump the transaction log using the no_log option.
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.
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.
This 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.
WARNING! If you use this option to extend the transaction log segment, you will not be able to get that space back later.
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.
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
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.
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.
All versions
