When performing dump and load database:
The database you are loading must have at least as much physical space as when it was dumped.
The physical space in the database you are loading is apportioned according to the physical fragments in the database that was dumped. This means a “hole”—an allocation unit for which there is no associated physical storage as a result of an earlier alter database log off command—in the database to be loaded does not necessarily remain a hole after the load.
Any leftover space in the database you are loading is apportioned in the same way as it is for dump and load database without holes.
You can determine the amount of physical space in the database that was dumped as well as whether it has holes, and information about the size and location of these holes, by running the load database with headeronly command.
Shrinking a log before
dump and load database
This scenario shrinks the log of a database that is then dumped. Use the load database with headeronly command and sp_helpdb system procedure to size the target database before it is loaded with the dump. The full sequence showing these commands is in “Example of sequence using dump and load database”.
Create a database with as many log devices as you need. The example creates two log devices.
(Optional) Run select * to confirm the database creation and show the device fragments that make up the database.
Remove unwanted portions of the log from the database without breaking the database dump sequence by using the alter database log off command. If the dump sequence of the database is already broken, alter database log off automatically removes any shrunken space from the end of the database. Any space removed that is not at the end of the database always becomes a hole.
In the example, the shrunken space in the middle of the database has become a hole.
The sysusages output shows the location and size of the holes (segmap = 0 and location of 4 in the example). The sp_helpdb output shows a summary of the size of the database excluding holes (9MB in the example) and the total size of the holes in the database (3072KB of log-only unavailable space or 3MB in the example):
The database to be loaded is 12MB in total, but of this, 9MB are actually physically in the database because of a 3MB hole. A dump database with headeronly command verifies that the database contains 12MB of logical pages and 9MB of physical pages. To load this database, you must create a new database at least 9MB in size.
Load the database and bring it online.
In the sysusages rows for the newly loaded database, the 9MB of physical space has been rearranged to match that of the dumped database so that the database is now 12MB in size, with only 9MB of physical pages and a 3MB hole.
