This section contains error messages for the alter database command.
16
Could not find enough space on disks to extend database %.*s.
This error occurs when an alter database command refers to an Adaptive Server device that is completely full.
If the device is not full but has less space than the alter database command requests, the database will be extended to fill up the available space on the device and no error message will be displayed. Instead, you will see the usual status message from alter database, but it will report fewer pages than you requested.
This error message does not usually appear in the error log. However, if the exhausted device also uses up the available physical disk drive space as well as the space allocated for Adaptive Server, kernel messages declaring the file system full will occur in the error log. If this happens, fix the file system problem as well as the lack of space for Adaptive Server.
To recover from Error 5006, do one of the following steps:
Use a device name in the alter database command which is large enough. Use sp_helpdevice and sp_helpdb to find out which devices have enough room.
Add a new device to Adaptive Server with the disk init command. Then refer to this device with the alter database command.
Although there is no particular performance benefit to keeping a database entirely on a single device, if you need to do this at your site, use the following procedure:
Back up all the databases on the device by dumping them to a valid dump device with the dump database command.
Drop the device with sp_dropdevice.
Initialize a larger device with disk init.
Create the old databases on the new device using standard procedure. It is important to create the databases exactly like the old ones or errors will occur. Refer to Error 2558 for more information.
Reload each database with the load database command.
Use the online database command for each database to make the databases available for use.
Refer to “Developing a Backup and Recovery Plan” in the System Administration Guide for information about how to safely create, dump, load and re-create databases.
All versions
16
Cannot extend the MASTER database onto any device other than 'master'. The ALTER DATABASE was aborted.
This error occurs when you try to extend the master database onto a device other than the master device.
It is recommended that you keep user objects out of
the master database. If you keep user databases
off the master device, you allow space in case the master database
needs to grow. In addition, if you ever need to rebuild the master
device, it will be easier if it does not contain user databases.
Adaptive Server users can move any “home-grown” system procedures that start with “sp_” to sybsystemprocs (by dropping them from the master database and creating them in sybsystemprocs).
Extend the master database only if absolutely necessary! If you are sure you must increase the master database size and have no room on the current master device, use the following procedure to remove user databases from the master device.
Dump the user databases with the dump database command.
Rename the dumped databases on the master device with sp_renamedb.
Re-create the databases with their original names on another device with create database. Be sure they are created exactly like the old databases, to avoid 2558 and other errors. Refer to Error 2558 for more information.
Load the dumps with load database.
Use the online database command for each database to make the databases available for use.
Check the databases in their new location to make sure the load was successful (that is, perform a simple query with isql), and if everything loaded successfully, drop the old databases from the master device.
You can now try to increase the size of the master database on the master device with the alter database command.
If the master device contains only the master database and the master device is too small, then use the following procedure:
WARNING! Altering the master device is extremely risky! Avoid it if at all possible. Be familiar with the recovery methods in Chapter 1, “System Database Recovery,” in case you lose your master database or master device.
Back up the master database with the dump database command.
Save the contents of key system tables such as sysdatabases, sysdevices, sysusages, and syslogins.
Use the buildmaster utility to build a new master device with enough extra space so that you will never need to increase the master device again. When buildmaster completes, a new master database will exist on the new master device.
Expand the size of the new master database with the alter database command, if necessary, so that it matches the size of the dumped master database.
Execute the following command in isql:
1> select name, high from master..sysdevices 2> where name = "master" 3> go
and note the “high” value for the master device.
Start Adaptive Server in single-user mode. Refer to “How to Start Adaptive Server in Single-User Mode” in the Encyclopedia of Tasks chapter for instructions.
Load the dump of the master database. Test the database to make sure it is functioning correctly before deleting any extra backups of master.
Allow updates to the system catalog:
1> sp_configure, "allow updates", 1 2> go
Reset the “high” value in master..sysdevices:
1> begin transaction 2> go
1> update master..sysdevices 2> set high = <value_of_high_from_step_5> 3> where name = "master" 4> go
If the previous update affected only one row, commit the transaction.
Restart Adaptive Server.
Turn off allow updates:
1> sp_configure, "allow updates", 0 2> go
All versions
17
Caution: You have set up this database to include space on disk %d for both data and the transaction log. This can make recovery impossible if that disk fails.
Error 5018 is raised when you execute alter database to add storage space, and the disk map for the database is found to contain both data and log segments.
The message can also be raised in another situation. When you load a database from dump, Adaptive Server compares the segments on the dump with the device allocations in the target database. If they match, the server maintains the allocations. If, however, there is a mismatch, the load will remap segments to the free device allocations. The remapping may result in mixed data and log segments, raising the 5018 error.
Why is recovery difficult when data and log are mixed? To understand this, consider first what happens when you keep data and log separate, and make a complete database backup (say) every 7 days. If the device containing the data crashes, you can restore the database from the latest backup, then apply the committed transactions from the log device, restoring the database to practically the last moments before failure.
In contrast, if data and log are mixed and the device fails, you can only restore the database up to the time of the last full backup, since there is no way to apply the transaction log.
Unless you are creating small, noncritical databases, always place the log on a separate device. This allows you to dump transaction logs, ensures full recovery from hard disk crashes and has many other benefits including performance improvement. For details, refer to “Placing the Transaction Log on a Separate Device” in the System Administration Guide.
It is likely that there is a mismatch in the segment mappings between the dump’s source and target databases. To correct this problem:
In the source server (where the dump is to be executed), run the following query:
1> use master
2> go
1> select * from sysusages where dbid=db_id("<database_name>")
2> go
Each line of output corresponds to a segment, with a segmap of 4 denoting a log segment, and any other segmap denoting a data segment. Save the output.
Dump the database.
Create the target database for load, specifying the segments in the same order and with the same sizes as defined by the query output in Step 1. For simplicity, make sure that all non-log segments have a segmap value of 3; if you had user-defined segments, the load will automatically remap these segments.
Check that the segment mappings are the same, by running the following query for the new database:
1> select * from sysusages where dbid=db_id("<database_name>")
2> go
Load the database.
All versions
16
ALTER DATABASE was successful. Cannot modify the last-chance threshold because the database ’%.*s’ is in read-only mode.
Each database that stores its transaction log on a separate segment has a last-chance threshold (LCT). This is an estimate of the number of free log pages that would be required to back up the transaction log.
Error 5034 is raised when you use alter database to extend log space on a read-only database. The database is altered and log space added, but the LCT cannot be modified because this requires an update to systhresholds, which is not possible in a read-only database.
Error 5034 is followed immediately by Error 5035 ("Remember to execute SELECT LCT_ADMIN(’LASTCHANCE’, %d), when the database is made writable.").
Version 11.0.3.2 and earlier raise Error 3906 ("Attempt
to BEGIN TRANsaction in database ’%.*s’ failed
because database is READ ONLY.") when alter database is
used to extend log space. The database is altered and log space
added, but the LCT cannot be modified. See the writeup of Error 3906 for details.
Re-establish the LCT after the database is available for updates using the following steps:
Use the following command to determine the new last-chance threshold:
1> use <database name>
2> go
1> select lct_admin("reserve", <log size in pages>)
2> go
For example, on a 20MB transaction log (containing 10240 2KB pages):
1> select lct_admin("reserve", 10240)
2> go
----------- 616
The resulting value is the amount of space required to dump the transaction log.
Modify the LCT using
1> select lct_admin(’lastchance’, <new threshold>) 2> go
Refer to Creating a Free-Space Threshold for the Log Segment in the System Administration Guide for additional information on setting thresholds.
For databases normally operated in read-only mode, no updates occur on the database and so there is no need to update the LCT.
All versions
