ID Number: Q82946
1.10 1.11
OS/2
Summary:
This article applies to SQL Server versions 1.1 and 1.11 only.
When moving databases between servers using the DUMP and LOAD
commands, the device fragments of the target database must match the
device fragments of the source database not only in size, but also in
sequence. If the device fragments are inconsistent, the integrity of
the LOAD may be compromised.
More Information:
A database may be fragmented on a single device or across multiple
devices. The fragmentation is dependent on the way in which the
database is created, logged, and altered.
For example, consider a 12-MB database consisting of 8-MB of data and
a 4-MB log. The database FRAGMENTED is created on SERVER1 with the
following commands:
CREATE DATABASE fragmented
ON dev1 = 2, dev2 = 2, dev3 = 2, dev4 = 2, log1 = 2, log2 = 2
EXEC SP_LOGDEVICE fragmented, log1
EXEC SP_LOGDEVICE fragmented, log2
FRAGMENTED is created on SERVER2 with the following commands:
CREATE DATABASE fragmented
ON dev1 = 4, log1 = 4, dev2 = 4
EXEC SP_LOGDEVICE fragmented, log1
Selecting segmap, lstart, and size from SYSUSAGES, where
dbid = DB_ID( 'fragmented' )
results in the following:
SERVER1
segmap lstart size
3 0 1024
3 1024 1024
3 2048 1024
3 3072 1024
4 4096 1024
4 5120 1024
SERVER2
segmap lstart size
3 0 2048
4 2048 2048
3 4096 2048
Note that a segmap value of 3 indicates a data segment and a segmap
value of 4 indicates a log segment. Lstart indicates the starting
logical address for this segment and size is the total number of 2K
data pages in the segment.
Comparing the values in the two tables reveals that only the first two
segments on SERVER1 correctly map to the first segment on SERVER2. The
remaining segments map to the wrong segmap for the corresponding
logical addresses. In other words, SERVER1's third segment
(lstart=2048) maps to SERVER2's second segment (lstart=2048). However,
SERVER1's third segment is for data, and SERVER2's second segment is
for log.
Because of the way LOAD works with SQL Server, some of SERVER1's data
may be loaded into SERVER2's log area, and some of SERVER1's log may
be loaded into SERVER2's data area. SQL Server merely copies
page-for-page when doing a load; for optimum speed, no checks are made
against a page's segment to prevent the mismatch above.
The LOAD will succeed with no indications of errors during the load or
upon completion. However, running DBCC CHECKALLOC indicates a problem,
most commonly an 2558 error (if one exists). Microsoft recommends
running DBCC CHECKDB and DBCC CHECKALLOC after the completion of all
loads.
Additional reference words: 1.00 Dumping and loading