The size of a log might change during a dump sequence in which the database is first dumped, and then transaction log dumps are performed periodically. This is particularly true, for example, if the log segment is increased to accommodate the increased volume of logging that is done by a fully logged select into, and then the log is shrunk after completion of the command to return the log to its former size. Use these guidelines to load such a dump sequence:
Create the database that is being loaded from the dumps with the largest size during the dump sequence. Determine this by executing dump tran with headeronly on the last transaction dump to be loaded.
Shrink the log to the size needed only after the transaction log sequence has completed and you have brought the database online.
Loading a dump sequence
of a database in which its log is shrunk
The example showing these commands and output follows the numbered steps, in “Example of sequence using dump and load transaction”.
Create the database. The example creates sales_db.
Turn on full logging of the database using the sp_dboption system procedure’s 'full logging for all' database option.
Dump the database.
Increase the size of the log segment using alter database log on in preparation for the execution of a fully logged select into command.
Run the fully logged select into command that makes use of the increased log segment.
Dump the transaction log to truncate the log to prepare for shrinking the log segment.
Shrink the database log using alter database log off to remove the log space added in the earlier step.
Dump the transaction log of the database with the shrunken log segment.
Before loading the sequence of dumps, get the logical size of the database from the last file in the load sequence. In the example, the size is16MB.
The logical size of the database from the last dump
in the load sequence is guaranteed to be at least as big as the
maximum physical size of the database throughout the dump sequence.
This provides a convenient method of determining what size the target
database should be to load all the dumps in the sequence.
Use the load transaction with headeronly command to determine the size that the target database must be, in order to accommodate all the dumps in the sequence.
Create a new database with as many log devices as you need. The example creates the sales_db1 database as a 16MB database with two log devices.
Load this database.
Load transaction logs from the first and second transaction log dumps into the database.
Bring the database online.
Reduce the size of the database by removing space from its log segment. In the example, the log segment is reduced in size by 10MB.
Run select * from sysusages to confirm the removal of space from the end of the database. The space that has been removed has become a hole in the database.
Use the with shrink_log option of dump database to remove the hole at the end of the database.
Run select * from sysusages again to confirm that Adaptive Server successfully removed the hole from the end of the database.
