This example shows the complete sequence you perform when using dump and load transaction, as described in “Loading a dump sequence of a database in which its log is shrunk”:
1> create database sales_db on sales_db_dev=3 log on sales_db_log1=3 2> go
00:00:00000:00018:2011/05/05 12:45:06.36 server Timestamp for database 'sales_db' is (0x0000, 0x00002aa9). 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). 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.
1> sp_dboption sales_db,'full logging for all',true 2> go
Database option 'full logging for all' turned ON for database 'sales_db'. Running CHECKPOINT on database 'sales_db' for option 'full logging for all' to take effect. (return status = 0)
1> use master 2> go 1> dump database sales_db to "/tmp/sales_db.dmp" 2> go
Backup Server session id is: 120. 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 /tmp/sales_db.dmp. Backup Server: 6.28.1.1: Dumpfile name 'ales_db11137014BC' section number 1 mounted on disk file '/tmp/sales_db.dmp' Backup Server: 4.188.1.1: Database sales_db: 852 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: 4.188.1.1: Database sales_db: 856 kilobytes (100%) DUMPED. Backup Server: 3.43.1.1: Dump phase number 3 completed. Backup Server: 4.188.1.1: Database sales_db: 860 kilobytes (100%) DUMPED. Backup Server: 3.42.1.1: DUMP is complete (database sales_db).
1> alter database sales_db log on sales_db_log2=10 2> go
Extending database by 5120 pages (10.0 megabytes) on disk sales_db_log2 Warning: The database 'sales_db' is using an unsafe virtual device 'sales_db_dev'. The recovery of this database can not be guaranteed. Warning: Using ALTER DATABASE to extend the log segment will cause user thresholds on the log segment within 128 pages of the last chance threshold to be disabled.
use sales_db go select * into bigtab2 from bigtab go
(20000 rows affected)
1> dump tran sales_db to "/tmp/sales_db.trn1" 2> go
Backup Server session id is: 9. 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 /tmp/sales_db.trn1. Backup Server: 6.28.1.1: Dumpfile name 'ales_db1113903D37' section number 1 mounted on disk file '/tmp/sales_db.trn1' Backup Server: 4.58.1.1: Database sales_db: 250 kilobytes DUMPED. Backup Server: 4.58.1.1: Database sales_db: 254 kilobytes DUMPED. Backup Server: 3.43.1.1: Dump phase number 3 completed. Backup Server: 4.58.1.1: Database sales_db: 258 kilobytes DUMPED. Backup Server: 3.42.1.1: DUMP is complete (database sales_db).
1> use master 2> go 1> alter database sales_db log off sales_db_log2 2> go
Removing 5120 pages (10.0 MB) from disk 'sales_db_log2' in database 'sales_db'.
1> dump tran sales_db to "/tmp/sales_db.trn2" 2> go
Backup Server session id is: 11. 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 /tmp/sales_db.trn2. Backup Server: 6.28.1.1: Dumpfile name 'ales_db1113903D87' section number 1 mounted on disk file '/tmp/sales_db.trn2' Backup Server: 4.58.1.1: Database sales_db: 6 kilobytes DUMPED. Backup Server: 3.43.1.1: Dump phase number 3 completed. Backup Server: 4.58.1.1: Database sales_db: 10 kilobytes DUMPED. Backup Server: 3.42.1.1: DUMP is complete (database sales_db).
1> load tran sales_db from "/tmp/sales_db.trn2" with headeronly 2> go
Backup Server session id is: 13. 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 'ales_db1113903D87' section number 1 mounted on disk file '/tmp/sales_db.trn2' This is a log dump of database ID 5, name 'sales_db', from May 19 2011 4:22AM. ASE version: lite_670673-1/Adaptive Server Enterprise/15.7.0/EBF 19186 SMP GA FS3b/B/x86_64/Enterprise Linux/asea. Backup Server version: Backup Server/15.7/EBF 19186 Drop#3B Prelim/B/Linux AMD Opteron/Enterprise Linux/aseasap/3556/64-bi. Database page size is 2048. Log begins on page 1986; checkpoint RID=Rid pageid = 0x7c2; row num = 0x14; previous BEGIN XACT RID=(Rid pageid = 0x7c2; row num = 0x4); sequence dates: (old=May 19 2011 4:21:11:356AM, new=May 19 2011 4:22:31:043AM); truncation page=1986; 123 pages deallocated; requires database with 8192 pages. Database log version=7; database upgrade version=35; database durability=UNDEFINED. segmap: 0x00000003 lstart=0 vstart=[vpgdevno=1 vpvpn=0] lsize=1536 unrsvd=192 segmap: 0x00000004 lstart=1536 vstart=[vpgdevno=2 vpvpn=0] lsize=1536 unrsvd=1530 Unavailable disk fragment: lstart=3072 lsize=5120 The database contains 8192 logical pages (16 MB) and 3072 physical pages (6MB).
1> create database sales_db1 on sales_db_dev=3 log on sales_db_log1=3, sales_db_log2=10 2> go
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 5120 logical pages (10.0 megabytes) on disk 'sales_db_log2' (5120 logical pages requested). Warning: The database 'sales_db1' is using an unsafe virtual device 'sales_db_dev'. The recovery of this database can not be guaranteed. Database 'sales_db1' is now online.
1> load database sales_db1 from "/tmp/sales_db.dmp" 2> go
Backup Backup Server session id is: 15. 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 'ales_db111390340B' section number 1 mounted on disk file '/tmp/sales_db.dmp' Backup Server: 4.188.1.1: Database sales_db1: 6148 kilobytes (37%) LOADED. Backup Server: 4.188.1.1: Database sales_db1: 6160 kilobytes (100%) LOADED. Backup Server: 3.42.1.1: LOAD is complete (database sales_db1). All dumped pages have been loaded. ASE is now clearing pages above page 3072, which were not present in the database just loaded. ASE has finished clearing database pages. Started estimating recovery log boundaries for database 'sales_db1'. Database 'sales_db1', checkpoint=(1863, 13), first=(1863, 13), last=(1865, 7). Completed estimating recovery log boundaries for database 'sales_db1'. Started ANALYSIS pass for database 'sales_db1'. Completed ANALYSIS pass for database 'sales_db1'. Started REDO pass for database 'sales_db1'. The total number of log records to process is 22. Redo pass of recovery has processed 2 committed and 0 aborted transactions. Completed REDO pass for database 'sales_db1'. Use the ONLINE DATABASE command to bring this database online; ASE will not bring it online automatically.
1> load tran sales_db1 from "/tmp/sales_db.trn1" 2> go
Backup Server session id is: 17. 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 'ales_db1113903D37' section number 1 mounted on disk file '/tmp/sales_db.trn1' Backup Server: 4.58.1.1: Database sales_db1: 250 kilobytes LOADED. Backup Server: 4.58.1.1: Database sales_db1: 258 kilobytes LOADED. Backup Server: 3.42.1.1: LOAD is complete (database sales_db1). Started estimating recovery log boundaries for database 'sales_db1'. Database 'sales_db1', checkpoint=(1863, 13), first=(1863, 13), last=(1986, 3). Completed estimating recovery log boundaries for database 'sales_db1'. Started ANALYSIS pass for database 'sales_db1'. Completed ANALYSIS pass for database 'sales_db1'. Started REDO pass for database 'sales_db1'. The total number of log records to process is 365. Redo pass of recovery has processed 8 committed and 0 aborted transactions. Completed REDO pass for database 'sales_db1'. Use the ONLINE DATABASE command to bring this database online; ASE will not bring it online automatically.
1> load tran sales_db1 from "/tmp/sales_db.trn2" 2> go
Backup Server session id is: 19. 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 'ales_db1113903D87' section number 1 mounted on disk file '/tmp/sales_db.trn2' Backup Server: 4.58.1.1: Database sales_db1: 10 kilobytes LOADED. Backup Server: 3.42.1.1: LOAD is complete (database sales_db1). Started estimating recovery log boundaries for database 'sales_db1'. Database 'sales_db1', checkpoint=(1986, 3), first=(1986, 3), last=(1986, 20). Completed estimating recovery log boundaries for database 'sales_db1'. Started ANALYSIS pass for database 'sales_db1'. Completed ANALYSIS pass for database 'sales_db1'. Started REDO pass for database 'sales_db1'. The total number of log records to process is 16. Redo pass of recovery has processed 2 committed and 0 aborted transactions. Completed REDO pass for database 'sales_db1'. Use the ONLINE DATABASE command to bring this database online; ASE will not bring it online automatically.
1> online database sales_db1 2> go
Started estimating recovery log boundaries for database 'sales_db1'. Database 'sales_db1', checkpoint=(1986, 20), first=(1986, 19), last=(1986, 20). Completed estimating recovery log boundaries for database 'sales_db1'. Started ANALYSIS pass for database 'sales_db1'. Completed ANALYSIS pass for database 'sales_db1'. Recovery of database 'sales_db1' will undo incomplete nested top actions. Started UNDO pass for database 'sales_db1'. The total number of log records to process is 2. Undo pass of recovery has processed 1 incomplete transactions. Completed UNDO pass for database 'sales_db1'. Database 'sales_db1' is now online.
1> alter database sales_db1 log off sales_db_log2 2> go
Removing 5120 pages (10.0 MB) from disk 'sales_db_log2' in database 'sales_db1'.
1> select * from sysusages where dbid=db_id("sales_db1")
2> go
dbid segmap lstart size vstart location unreservedpgs crdate vdevno ---- ------ ------- ---- ------ -------- -------------- ------------------ ------ 6 3 0 1536 1536 0 192 May 19 2011 4:25AM 1 6 4 1536 1536 1536 0 1536 May 19 2011 4:25AM 2 6 0 3072 5120 3072 4 5100 May 19 2011 4:25AM -6 (3 rows affected)
1> dump database sales_db1 to "/tmp/sales_db1.dmp" with shrink_log 2> go
Backup Server session id is: 22. 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 /tmp/sales_db1.dmp. Backup Server: 6.28.1.1: Dumpfile name 'sales_db11113903EC3' section number 1 mounted on disk file '/tmp/sales_db1.dmp' Backup Server: 4.188.1.1: Database sales_db1: 3100 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_db1: 3108 kilobytes (100%) DUMPED. Backup Server: 3.42.1.1: DUMP is complete (database sales_db1).
1> select * from sysusages where dbid=db_id("sales_db1")
2> go
dbid segmap lstart size vstart location unreservedpgs crdate vdevno ---- ------ ------- ---- ------ -------- -------------- ------------------- ------ 6 3 0 1536 1536 0 192 May 19 2011 4:25AM 3 6 4 1536 1536 1536 0 1530 May 19 2011 4:25AM 4 (2 rows affected)
