Shrinking a Log Before a dump and load database

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 described 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.

This example shows the complete sequence you perform when using dump and load database

create database sales_db on sales_db_dev=3 log on sales_db_log1=3, 
    sales_db_log2=3, sales_db_log1=3, sales_db_log2=3

00:00:00000:00015:2011/01/21 09:38:28.29 server  Timestamp for database 
'sales_db' is (0x0000,
CREATE DATABASE: allocating 1536 logical pages (3.0 megabytes) on disk 'sales_db_dev'
(1536 logical pages requested).
CREATE DATABASE: allocating 1536 logical pages (3.0 megabytes) on disk 'sales_db_log1' 
(1536 logical pages requested).
CREATE DATABASE: allocating 1536 logical pages (3.0 megabytes) on disk 'sales_db_log2' 
(1536 logical pages requested).
CREATE DATABASE: allocating 1536 logical pages (3.0 megabytes) on disk 'sales_db_log1' 
(1536 logical pages requested).
CREATE DATABASE: allocating 1536 logical pages (3.0 megabytes) on disk 'sales_db_log2' 
(1536 logical pages requested).
Warning: The database 'sales_db' is using an unsafe virtual device 'sales_db_dev'. 
The recovery of this database can not be guaranteed.
Database 'sales_db' is now online.

select * from sysusages where dbid=4

dbid segmap lstart size vstart location unreservedpgs  crdate            vdevno
---- ------ ------ ---- ------ -------- -------------  ------            ------
    4      3      0 1536      0       0           670  Jan 21 2011 9:38AM     1
    4      4   1536 1536      0       0          1530  Jan 21 2011 9:38AM     2
    4      4   3072 1536      0       0          1530  Jan 21 2011 9:38AM     3
    4      4   4608 1536   1536       0          1530  Jan 21 2011 9:38AM     2
    4      4   6144 1536   1536       0          1530  Jan 21 2011 9:38AM     3

(5 rows affected)

alter database sales_db log off sales_db_log2
select * from sysusages where dbid=4

dbid segmap lstart size vstart location unreservedpgs  crdate            vdevno
---- ------ ------ ---- ------ -------- -------------  ------            ------
    4     3      0 1536      0        0          670  Jan 21 2011 9:38AM     1
    4     4   1536 1536      0        0         1530  Jan 21 2011 9:38AM     2
    4     0   3072 1536   3072        4         1530  Jan 21 2011 9:38AM    -4
    4     4   4608 1536   1536        0         1530  Jan 21 2011 9:38AM     2

(4 rows affected)

sp_helpdb sales_db

 name     db_size       owner dbid  created      durability status
 -------- ------------- ----- ----  ------------ ---------- --------------
sales_db  9.0 MB        sa       4  Jan 21, 2011 full       no options set

(1 row affected)
device_fragments   size    usage      created               free kbytes
------------------ ------  ---------- ------------------    --------------
sales_db_dev       3.0 MB  data only  Jan 21 2011 9:38AM              1340
sales_db_log1      3.0 MB  log only   Jan 21 2011 9:38AM    not applicable
sales_db_log1      3.0 MB  log only   Jan 21 2011 9:38AM    not applicable

--------------------------------------------------------------
log only free kbytes = 6082, log only unavailable kbytes = 3072
(return status = 0)

dump database sales_db to "c:/temp/sales_db.dmp"

Backup Server session id is: 45. Use this value when executing the 'sp_volchanged' system
stored procedure after fulfilling any volume change request from
the Backup Server.
Backup Server: 4.41.1.1: Creating new disk file c:/temp/sales_db.dmp.
Backup Server: 6.28.1.1: Dumpfile name 'sales_db11021087C7  ' section number 1
mounted on disk file 'c:/temp/sales_db.dmp'
Backup Server: 4.188.1.1: Database sales_db: 848 kilobytes (67%) DUMPED.
Backup Server: 4.188.1.1: Database sales_db: 862 kilobytes (100%) DUMPED.
Backup Server: 3.43.1.1: Dump phase number 1 completed.
Backup Server: 3.43.1.1: Dump phase number 2 completed.
Backup Server: 3.43.1.1: Dump phase number 3 completed.
Backup Server: 4.188.1.1: Database sales_db: 870 kilobytes (100%) DUMPED.
Backup Server: 3.42.1.1: DUMP is complete (database sales_db).

load database sales_db from "c:/temp/sales_db.dmp" with headeronly

Backup Server session id is: 48. Use this value when executing the 'sp_volchanged' system
stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 6.28.1.1: Dumpfile name 'sales_db11021087C7  ' section number 1 mounted
on disk file 'c:/temp/sales_db.dmp'
This is a database dump of database ID 4, name 'sales_db', from Jan 21 2011 9:39AM. ASE
version: lite_642236-1/Adaptive Server Enterprise/15.7/EBF 18567 SMP
Drop#2/B/X64/Windows Server/aseasap/ENG/. Backup Server version: Backup
Server/15.7/B/X64/Windows Server/aseasap/ENG/64-bit/DEBUG/Thu Jan 20 11:12:51 2011.
Database page size is 2048.
Database contains 6144 pages; checkpoint RID=(Rid pageid = 0x604; row num = 0x12); next
object ID=560001995; sort order ID=50, status=0; charset ID=2.
Database log version=7; database upgrade version=35; database durability=UNDEFINED.
segmap: 0x00000003 lstart=0 vstart=[vpgdevno=1 vpvpn=0] lsize=1536 unrsvd=670
segmap: 0x00000004 lstart=1536 vstart=[vpgdevno=2 vpvpn=0] lsize=1536 unrsvd=1530
Unavailable disk fragment: lstart=3072 lsize=1536
segmap: 0x00000004 lstart=4608 vstart=[vpgdevno=2 vpvpn=1536] lsize=1536 unrsvd=1530
The database contains 6144 logical pages (12 MB) and 4608 physical pages (9 MB).

create database sales_db2 on sales_db_dev=3 log on sales_db_log1=6

CREATE DATABASE: allocating 1536 logical pages (3.0 megabytes) on disk 'sales_db_dev'
(1536 logical pages requested).
CREATE DATABASE: allocating 3072 logical pages (6.0 megabytes) on disk 'sales_db_log1'
(3072 logical pages requested).
Warning: The database 'sales_db2' is using an unsafe virtual device 'sales_db_dev'. 
The recovery of this database can not be guaranteed.
Database 'sales_db2' is now online.

select * from sysusages where dbid=db_id("sales_db2")

dbid segmap lstart size vstart location unreservedpgs crdate             vdevno
---- ------ ------ ---- ------ -------- ------------- ------             ------
   5      3      0 1536   1536        0           670 Jan 26 2011 1:22AM      1
   5      4   1536 3072   3072        0          3060 Jan 26 2011 1:22AM      2

load database sales_db2 from "/tmp/sales_db.dmp"

Backup Server session id is: 10. Use this value when executing the 'sp_volchanged' system
stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 6.28.1.1: Dumpfile name 'sales_db1102602564  ' section number 1 mounted
on disk file '/tmp/sales_db.dmp'
Backup Server: 4.188.1.1: Database sales_db2: 6148 kilobytes (33%) LOADED.
Backup Server: 4.188.1.1: Database sales_db2: 9222 kilobytes (50%) LOADED.
Backup Server: 4.188.1.1: Database sales_db2: 9230 kilobytes (100%) LOADED.
Backup Server: 3.42.1.1: LOAD is complete (database sales_db2).
Started estimating recovery log boundaries for database 'sales_db2'.
Database 'sales_db2', checkpoint=(1544, 22), first=(1544, 22), last=(1544, 22).
Completed estimating recovery log boundaries for database 'sales_db2'.
Started ANALYSIS pass for database 'sales_db2'.
Completed ANALYSIS pass for database 'sales_db2'.
00:00:00000:00011:2011/01/26 05:12:15.86 server  Log contains all committed transactions
until 2011/01/26 01:55:15.71 for database sales_db2.
Started REDO pass for database 'sales_db2'. The total number of log records to process
is 1.
Completed REDO pass for database 'sales_db2'.
00:00:00000:00011:2011/01/26 05:12:15.88 server  Timestamp for database 'sales_db2' is
(0x0000, 0x00001612).
Use the ONLINE DATABASE command to bring this database online; ASE will not bring it
online automatically.

online database sales_db2

Started estimating recovery log boundaries for database 'sales_db2'.
Database 'sales_db2', checkpoint=(1544, 22), first=(1544, 22), last=(1544, 22).
Completed estimating recovery log boundaries for database 'sales_db2'.
Started ANALYSIS pass for database 'sales_db2'.
Completed ANALYSIS pass for database 'sales_db2'.
00:00:00000:00011:2011/01/26 05:12:22.49 server  Log contains all committed transactions
until 2011/01/26 01:55:15.71 for database sales_db2.
Recovery of database 'sales_db2' will undo incomplete nested top actions.
Database 'sales_db2' is now online.

select * from sysusages where dbid=db_id("sales_db2")

dbid segmap lstart size vstart location unreservedpgs crdate             vdevno
---- ------ ------ ---- ------ -------- ------------- ------             ------
   5      3      0 1536   1536        0           670 Jan 26 2011 5:12AM      1
   5      4   1536 1536   3072        0          1530 Jan 26 2011 5:12AM      2
   5      0   3072 1536   3072        4          1530 Jan 26 2011 5:12AM     -5
   5      4   4608 1536   4608        0          1530 Jan 26 2011 5:12AM      2