Post by psr1218I've been attempting to load a very large compressed backup into an
archivedb but the load fails with a log suspend on the
scratch_database. I've extended the log several times without load
success.
Does anyone have experience calculating the correct scratch_database
log size for database loads exceeding 900Gb?
Thanks...
Here are my complete notes on the subject. You will see that the
scratch_db needs to be a decent size.
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~#
Archive Databases
Databases must be dumped using the new syntax…
dump databases dbname to '/path/dbname_1.cmp’
stripe on '/path/dbname_2.cmp’
with compression=’1’
go
Create Archive Databases
First you need to create a small scratch database which will hold meta
data for the archive database. The only interesting thing with it is
that after you have built it you need to set the dboption scratch
database to true for it. It needs to be quite large. The example below
is based on a 220Gb DB.
create database scratch_db on datadev1 = 5000
log on logdev1 = 2000
go
exec sp_dboption "scratch_db", "scratch database", "true"
exec sp_dboption "scratch_db", "trunc log on chkpt", "true"
go
WATCH-OUT
If you are loading from a compressed dump then you need to set the
configuration parameter compression memory size to at least 64. You
may also need to increase your procedure cache. The scratch database
needs to be around 5GB data/2GB Log for the recovery of a 220Gb
database.
Make sure you change the ownership of the database to match the
database to be restored prior to starting the load. This cannot be
altered later and may be important for user access. One way is to give
the DB owner sa_role, create the DB, then revoke the sa_role.
The progress of the load is not shown in the backuplog. You can only
tell if it is working by checking the physical_io from sysprocesses.
Syntax
The syntax for creating the archive database is (as DB owner):
create archive database archive_db on datadev1=1000
with scratch_database = scratch_db
go
USE archive_db
go
EXEC sp_changedbowner 'dbname'
go
You can then load the dump into the archive database with
Load database archive_db from "/dumps/dbname.dmp"
Go
Online database archive_db
go
The database is now ready for browsing and you can also run dbcc
checks etc against it.