Shrinking Log Space

Shrinking a database log involves dumping the database and log, altering the size of the database, and loading the database and log dumps.

  1. Create the database. The example creates sales_db.
  2. Turn on full logging of the database using the sp_dboption system procedure’s 'full logging for all' database option.
  3. Dump the database.
  4. Increase the size of the log segment using alter database log on in preparation for the execution of a fully logged select into command.
  5. Run the fully logged select into command that makes use of the increased log segment.
  6. Dump the transaction log to truncate the log to prepare for shrinking the log segment.
  7. Shrink the database log using alter database log off to remove the log space added in the earlier step.
  8. Dump the transaction log of the database with the shrunken log segment.
  9. 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.
    Note: 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.

  10. 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.
  11. Load this database.
  12. Load transaction logs from the first and second transaction log dumps into the database.
  13. Bring the database online.
  14. 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.
  15. 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.
  16. Use the with shrink_log option of dump database to remove the hole at the end of the database.
  17. Run select * from sysusages again to confirm that SAP ASE successfully removed the hole from the end of the database.
This example shows the complete sequence you perform when using dump and load transaction.
create database sales_db on sales_db_dev=3 log on sales_db_log1=3
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.
sp_dboption sales_db,'full logging for all',true
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)
use master
dump database sales_db to "/tmp/sales_db.dmp"
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).
alter database sales_db log on sales_db_log2=10
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
select * into bigtab2 from bigtab
(20000 rows affected)
dump tran sales_db to "/tmp/sales_db.trn1"
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).
use master
alter database sales_db log off sales_db_log2
Removing 5120 pages (10.0 MB) from disk 'sales_db_log2' in database 'sales_db'.
dump tran sales_db to "/tmp/sales_db.trn2"
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).
load tran sales_db from "/tmp/sales_db.trn2" with headeronly
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).
create database sales_db1 on sales_db_dev=3 log on sales_db_log1=3, sales_db_log2=10
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.
load database sales_db1 from "/tmp/sales_db.dmp"
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.
load tran sales_db1 from "/tmp/sales_db.trn1"
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.
load tran sales_db1 from "/tmp/sales_db.trn2"
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.
online database sales_db1
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.
alter database sales_db1 log off sales_db_log2
Removing 5120 pages (10.0 MB) from disk 'sales_db_log2' in database 'sales_db1'.
select * from sysusages where dbid=db_id("sales_db1")
 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)
dump database sales_db1 to "/tmp/sales_db1.dmp" with shrink_log
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).
select * from sysusages where dbid=db_id("sales_db1")
 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)
Related concepts
Using the dump and load Commands