http://www.sybase.com/detail?id=1324
S
EGMENT
R
EMAPPING
WITH
LOAD
DATABASE
W
HEN
M
OVING
A
D
ATABASE
To safely load an old database onto a new
database, create the target database exactly the
same as the source.
Contents
To move a database to new devices on your system or to rebuild a database on the old devices, you have to first create the new database, then load a dump of the old database onto it. SQL Server preserves the logical structure of the source database when it is loaded onto the target database. From System 10 on, SQL Server also preserves the segment mapping.
In order to successfully load the dump, you have to create the target database to be identical to the source, with devices of the same size and in the same order. If you can't or don't create the
database just like the source, you may get unexpected results when you the load the database. This TechNote discusses potential load database problems. It contains the following sections:
Terminology
History of How load Handles Fragments
Unexpected Results From load database
Explanation of Results
How to Avoid Problems When Moving or Re-creating Databases
ReferencesTerminology
This TechNote uses the following terminology:
Source database the original database.
Target database the new database onto which a dump of the source database is loaded. Fragment a disk piece containing part or all of a database. It is represented by a row in the sysusages table in the master database. The create database and alter database commands
create new rows in sysusages. For example, this command:
1> create database source_db
2> on dev1 = 2
3> log on dev2 = 2
4> go
creates two fragments, therefore two rows in sysusages.
Segment a label used to indicate the type of data that can be put on a fragment. Segments 0, 1,
and 2 are always the system, default, and log segments. Segments 3 and following are user- defined segments. Segments for a database are represented by rows in the syssegments table of the database.
Segments are logical. They are a way of organizing data in a database. You can place segments on fragments, then associate objects with the segments. Segments do not have a one-to-one
relationship with physical space. You can place two or more segments on a fragment, or you can extend a segment over several fragments.
sysusages table the sysusages table in the master database keeps track of database fragments. It contains one row for each fragment of all your databases. Query sysusages as follows:
1> use master
2> go
1> select dbid,
segmap
, lstart,
size,
vstart
2> from sysusages where dbid = 5
3> go
dbid
segmap
lstart
size
vstart
----
---
---
-
---
---5
3
0
1024
33554432
5
4
1024
1024
50331648
5
8
2048
2048
67108864
5
16
4096
2048
83886080
(4 rows affected)
The columns in this output are:dbid the database ID number.
segmap a bitmapped representation of the values of all segments associated with a disk fragment.
Figure 1 shows how the segmap value in sysusages is determined:
and second positions of the bitmap are turned on, for a value of 3.
lstart The logical page number on which a database fragment begins. Logical pages are used by the
database. The first fragment of a database has an lstart value of 0.
size The fragment size is in 2K pages. To get the number of megabytes, divide the size by 512. vstart The virtual page number on which a fragment begins. Virtual pages are used by the database
device. The vstart values for a database on several devices have no relationship to each other; they describe the location of the fragment on the device as an offset in 2K pages.
Note
For more information about segments and placing databases on devices, see the SQL Server System
Administration Guide.
History of How load Handles Fragments
The way that SQL Server handled fragments changed with System 10, as described in the following sections.
Pre-System 10 load Functionality
Before release 10.0, SQL Server preserved the logical page order but not the segment mapping of a database during a load. Loading a database dump onto a target database that you created
differently than the source database¯with differently sized fragments or fragments created in a different order¯could result in Error 2558 (for example, log-on-data or data-on-log). Figure 2 shows how Error 2558 might occur. In this example, the segments were placed on the target database in a different order than on the source, resulting in data pages on a log segment and log pages on a data segment.
Figure: Error 2558 in pre-System 10 load
Note
See the SQL Server Error Messages manual for more information about Error 2558.
System 10 Change in load Functionality
In System 10, Sybase changed load functionality to avoid the 2558 error. Now when you load a dump onto a target database, the target acquires the segment mapping of the source; any segments that you may have placed on the target database are overwritten.
Unexpected Results From load database
While data mixing no longer happens in the current release of SQL Server, you can still get unexpected results if you move a database spanning several devices onto a new set of devices where the fragments were created differently than on the original database. The ways you can create a database differently are:
Creating the database with fragments of different sizes than the source database
Creating the fragments in a different order than the source if the fragments are of varying sizesUnexpected results include:
Disk fragmentation more fragments than you started with
Data and log fragments on one device
A database that is impossible to re-create with create databaseExample
The following example demonstrates how loading a dump onto a database created with differently sized fragments or fragments in a different order than the source database can increase the number of fragments and place data and log on the same device.
Creating source_db
The source database, source_db, was created as follows:
1> create database source_db
2> on dev1 = 2
3> log on dev2 = 2
4> go
Later, two more devices were added to the database:
1> alter database source_db
2> on dev3 = 4,
3> dev4 = 4
4> go
Then two user-defined segments, seg1 and seg2, were added to the new devices:
2> go
1> sp_addsegment seg2, source_db, dev4
2> go
Finally, the system and default segments were dropped from dev3 and dev4:
1> sp_dropsegment system, source_db, dev3
2> go
1> sp_dropsegment "default", source_db, dev3
2> go
1> sp_dropsegment system, source_db, dev4
2> go
1> sp_dropsegment "default", source_db, dev4
2> go
A select from sysusages shows four fragments for source_db:
1> select dbid,
segmap
, lstart,
size,
vstart
2> from sysusages where dbid = 5
3> go
dbid
segmap
lstart
size
vstart
----
---
---
-
---
---5
3
0
1024
33554432
5
4
1024
1024
50331648
5
8
2048
2048
67108864
5
16
4096
The segmap column shows the two user-defined segments, seg1 and seg2, as values 8 and 16 respectively.
The database fragments now look like this:
Figure: source_db fragments
Creating target_db
target_db was created differently than source_db. The database was created in a single command
on all its devices at once instead of being altered onto them later. This placed the log segment last and resulted in a different order of fragment sizes:
1> create database target_db
2> on dev5 = 2,
3> dev6 = 4,
4> dev7 = 4
5> log on dev8 = 2
6> go
User-defined segments seg1 and seg2 were added to dev6 and dev7:
2> go
1> sp_addsegment seg2, target_db, dev7
2> go
The system and default segments were dropped from dev6 and dev7:
1> sp_dropsegment system, source_db, dev6
2> go
1> sp_dropsegment "default", source_db, dev6
2> go
1> sp_dropsegment system, source_db, dev7
2> go
1> sp_dropsegment "default", source_db, dev7
2> go
A select from sysusages shows four fragments for target_db:
1> select dbid,
segmap
, lstart,
size,
vstart
2> from sysusages where dbid = 6
3> go
dbid
segmap
lstart
size
vstart
----
---
--
---
-
---
---6
3
0
1024
100663296
6
8
1024
2048
117440512
6
16
3072
2048
134217728
6
4
5120
1024
Figure 5 shows target_db's fragments as they were created:
Figure: target_db with fragments in the order created
Loading Dump from source_db onto target_db
To continue the example, source_db is dumped to tape and the dump loaded onto target_db with the
load database command:
load database target_db from "/devices/source_dump"
Two things happen:
SQL Server overwrites the segments originally added to target_db with the segment map ofsource_db.
Because more than one segment is now assigned to the same fragment, SQL Server divides the fragment into smaller fragments, each with its own segment.Figure: target_db acquires segment map from source_db
A select from sysusages now shows six disk fragments for target_db:
1> select dbid,
segmap
, lstart,
size,
vstart
2> from sysusages where dbid = 6
3> go
dbid
segmap
lstart
size
vstart
----
---
---
-
---
6
3
0
1024
100663296
6
4
1024
1024
117440512
6
8
2048
1024
117441536
6
8
3072
1024
134218752
6
16
5120
1024
150994944
Loading the dump of source_db onto target_db caused the following unexpected results:
Log and data are on the same physical device, although not mixed on the same fragment.
The number of disk fragments increased from four to six.Explanation of Results
The results in the example are due to the way SQL Server handles loads and how it resolves
differences in the way databases are created. When you move a database to a new set of devices or re-create it on the same devices, the physical and logical storage is determined as follows:
The target database determines the physical storage, that is, how much space is allocated on which database devices.
The source database determines logical storage, that is, the logical page order and segmap values of the fragments. This occurs whether or not you have defined segments on the new fragments with sp_addsegment and sp_extendsegment.Resolving Differences
When a database dump is loaded onto a database that was created differently, SQL Server resolves the differences as follows:
The load process overwrites any segment mapping you may have created on the target database.
If data which was on different fragments with different segmaps on separate devices is now placed on the same device, SQL Server will create separate fragments on the device for each fragment. In other words, it will add rows to sysusages with different segmap values.
If data which was the same segment on separate devices is placed on contiguous fragments on the same device, SQL Server will merge the fragments. In other words, it will delete the separate rows in sysusages and insert a single row with a larger size.Databases That Cannot Be Re-Created
If you made a size error when initializing a device and later created a database on the device, you could have a database that cannot be created with the create or alter database commands. When you initialize a device with the disk init command, you must specify a size in 2K pages. One megabyte is 512 2K pages. If you enter a device size that is not divisible by 512, you may create a device whose size is not in whole megabytes. SQL Server allocates space in units of 256 pages, or 1/2MB, so your device may have a size that includes 1/2MB.
If you create a database that is too large to fit on the device, SQL Server uses all the space available, so you get a database whose size also includes 1/2MB.
If you have such a database, you may not experience any problems until you try to move or rebuild it. The create and alter database commands allow you to enter only whole megabytes to specify database size. Therefore you cannot create a new database exactly the size of the old one. If you load the old database onto the new, SQL Server has to remap the fragments, creating 1/2MB fragments.
Similarly, if you create the target database on a device whose size includes 1/2MB, and try to load a dump of a database whose size is in whole megabytes, you get 1/2MB fragments.
Summary
The unintended results of loading a source database onto a target database that was created differently include:
Log and data fragments on the same device
An unpredictable number of fragments
If you have 1/2MB fragments, a database which you cannot re-create in exactly the same wayHow to Avoid Problems When Moving or Re-creating
Databases
The only way to avoid unexpected results when moving a database is to create the new database with fragments in the same size and order as the source. The following methods allow you to do this.
Gather Information About Your Database
To get a complete picture of how your source database was created¯what devices it's on, what segments it has, and what size the fragments are¯you can gather information from the following system tables:
sysdevices
sysdatabases
sysusages
syssegmentsFollow these steps to get information about your database:
1. To see what segments are on the database, select from the syssegments table:
5.
2> go
6.segment
name
7.---
---
8.0
system
9.1
default
10.2
logsegment
11.3
seg1
12.4
seg2
13. 14.(5 rows affected)
15. Determine your database's ID number:
16.
1> use master
17.
2> go
18.
1> select name, dbid from sysdatabases
19.
2> where name = source_db
20.
3> go
21.name
dbid
22.---
----23.master
1
24.model
3
25.source_db
5
26.sybsystemprocs
4
27.
target_db
6
28.
tempdb
2
29.
30.
(6 rows affected)
31. Use the database ID to select from the sysusages table. The sysusages table shows you the order the fragments were created, their sizes, and their segmap values:
32.
1> select dbid, segmap, lstart, size, vstart
33.
2> from sysusages where dbid = 5
34.
3> go
35.
dbid
segmap
lstart
size
vstart
36.----
---
--
---
---
---37.5
3
0
1024
33554432
38.5
4
1024
1024
50331648
39.5
8
2048
2048
67108864
40.5
16
4096
2048
83886080
41. 42.(4 rows affected)
Verify that all your size values are divisible by 512, that is, that they are whole megabytes.
WARNING!
If you have fragments whose sizes are not divisible by 512, you cannot re-create the database. If you want to avoid 1/2MB fragments, do not try to load it onto another database. Call Technical
sysusages will fit between the high and low virtual page numbers of one of the devices.
2.
1> select low, high, name from sysdevices
3.
2> go
4.low
high
name
5.---
----6.33554432
33556479
dev1
7.50331648
50333695
dev2
8.67108864
67110911
dev3
9.83886080
83888127
dev4
10.100663296
100665343
dev5
11.117440512
117442559
dev6
12.134217728
134219775
dev7
13.150994944
150996991
dev8
Figure 7 shows the relationship between the system tables that give you information about your databases.
Figure: Relationship between system tables that show space usage
Initializing Devices Correctly
When you initialize your new devices, be sure that the size you specify is divisible by 512. 1. Initialize the disk. Use a command similar to this:
2.
1> disk init name = "dev5",
3.
2> physname = "/work/sybase/devices/dev5",
4.
3> vdevno = 6,
7.
1> use master
8.
2> go
9.
1> select low, high, name from sysdevices
10.
2> where name = dev5
11.
3> go
12.low
high
name
13.---
----14.
100663296
100665343
dev5
15. Use this equation to see if the size of the device is a whole megabyte: 16.
(high low + 1) / 512
If the device size is not a whole megabyte, drop the device with sp_dropdevice. See the
System Administration Guide for more information on initializing devices.
Creating the Database Correctly
Follow these steps to create the target database:
1. Refer to the output from the sysusages table for the source database as you create the new database.
2. If you have the script which created the source database, run it now, then go to step 5. 3. Create your database with the fragments in exactly the same order as the source database.
The database in the long example must be created in two steps if you use the log on option. This is necessary because log is on the second fragment, but the log on option cannot be used as the second phrase of a create statement.
Depending on where your log segment is, use commands like the following:
1> create database target_db
2> on dev5 = 2
4> for load
5> go
1> alter database target_db
2> on dev7 = 4,
3> dev8 = 4
4> for load
5> go
You do not need to add segments. The target database will acquire the segment mapping of the source database.
4. Check the messages from the create and alter commands. Verify that the pages allocated are divisible by 512.
WARNING!
If the number of allocated pages is not divisible by 512, drop the database. Check the sysdevices table in the master database for the page ranges of the device. Use this equation to see if the size of the device is a whole megabyte:
(high low + 1) / 512
If the device size is not a whole megabyte, drop the device with sp_dropdevice. See the System
Administration Guide for more information on initializing devices.
1. Perform a select from sysusages now:
2.
1> select dbid, segmap, lstart, size, vstart
3.
2> from sysusages where dbid = 6
4.
3> go
5.
dbid
segmap
lstart
size
vstart
6.
----
---
1024
100663296
8.6
4
1024
1024
117440512
9.6
3
2048
2048
134217728
10.6
3
4096
2048
150994944
11. 12.(4 rows affected)
Verify that the target database has the same number of fragments as the source and that the size and lstart values are the same as those of the source database.
Note
dbid and vstart values are normally different than those of the source database. This doesn't matter.
1. Load the database dump from the source database with a command like the following.
2.
load database target_db from "/devices/source_dump"
3. Look at sysusages again after the load:
4.
1> select dbid, segmap, lstart, size, vstart
5.
2> from sysusages where dbid = 6
6.
3> go
7.
dbid
segmap
lstart
size
vstart
8.
----
---
---
---
9.
6
3
0
10.
6
4
1024
1024
117440512
11.6
8
2048
2048
134217728
12.6
16
4096
2048
150994944
13. 14.(4 rows affected)
Verify that the target database now has the same segmap values as the source database.
Alternative Method to Create the Database
You can save time by creating the target database in a single step. While this method is not identical to the one you used to create the source database, the results are the same.
1. Refer to the output of the sysusages table for the source database and create your database with a command like the following, specifying only devices and sizes:
2.
1> create database target_db
3.
2> on dev5 = 2,
4.3> dev6 = 2,
5.4> dev7 = 4,
6.5> dev8 = 4
7.6> for load
8.7> go
Do not use the log on option and do not add any segments. Segment mapping will come from the source database when it is loaded.
9. Perform a select from sysusages now:
13.
dbid
segmap
lstart
size
vstart
14.----
---
---
---
15.6
7
0
1024
100663296
16.6
7
1024
1024
117440512
17.6
7
2048
2048
134217728
18.6
7
4096
2048
150994944
19. 20.(4 rows affected)
Verify that the number of fragments is correct and the size and lstart values are the same as those of the source database.
Note
dbid and vstart values usually differ from those of the source database. This is not a problem.
The segmap values default to 7. These values will change after the load. 1. Load the database dump from the source database.
2.
load database target_db from "/devices/source_dump"
3. Look at sysusages again after the load:
4.
1> select dbid, segmap, lstart, size, vstart
5.
2> from sysusages where dbid = 6
6.
3> go
7.
dbid
segmap
lstart
8.
----
---
---
---
9.6
3
0
1024
100663296
10.6
4
1024
1024
117440512
11.6
8
2048
2048
134217728
12.6
16
4096
2048
150994944
13. 14.(4 rows affected)
Verify that the target database now has the same segmap values as the source database.
References
See the SQL Server Reference Manual for command and stored procedure syntax. See the System
Administration Guide for a discussion of segments.