Deletes and updates rows in the sysusages system table and combines sysusage entries where possible.
sp_coalesce_fragments [DBNamePattern]
where
For more information about the LIKE operator, see Wildcard Characters in the Microsoft SQL Server Transact-SQL Reference.
System administrators sometimes load a database or table backup file from a production database into a database on another server. Requirements for cross-database dump and load activities are documented under the LOAD Statement in the Microsoft SQL Server Transact-SQL Reference. One requirement is that two databases must have compatible lpage (logical page) and segmap (segment map) column information in the sysusages table. The target database must be created so the segmap for each range of logical pages (lpage) is compatible with the same information in the dumped database.
Two databases have compatible lpage and segmap structures if for every lpage number they have the same segmap value. If one database is larger than the other, some of the larger database's lpage numbers will not exist for the smaller database. A smaller database can be dumped into a larger database, but a larger database cannot be dumped into a smaller database.
Important If the target and destination databases have different segmap structures, dump or load operations can cause data access problems, error messages can appear, or the load can fail if recovery must run.
For more information about logical pages, see the Microsoft SQL Server Administrator's Companion.
The sp_coalesce_fragments procedure simplifies the output from sp_help_revdatabase by replacing multiple rows in the sysusages system table with one row. For every row in the sysusages system table, sp_help_revdatabase produces an ALTER DATABASE statement. With fewer rows in the sysusages system table, fewer ALTER DATABASE statements are produced by sp_help_revdatabase, and fewer rows in sysusages makes it easier to verify compatible logical page and segment map combinations between databases. System administrators can execute sp_coalesce_fragments after ALTER DATABASE statements are issued.
This stored procedure is used with sp_help_revdatabase. Output from sp_help_revdatabase can help create a compatible database.
For more information about creating a compatible database, see sp_help_revdatabase later in this document.
The sysusages system table records the device fragments defined for each database during the CREATE DATABASE and ALTER DATABASE statements. Sometimes several fragments for a database on one device are physically contiguous. The sysusages rows for these contiguous fragments can be reduced to one row by running sp_coalesce_fragments. This reduction is safe because the fragments for a database on any device always have the same value for sysusages.segmap.
For more information about creating a database script that can replicate an existing database structure, see sp_help_revdatabase later in this document.
For more information about altering a database, see the ALTER DATABASE statement in the Microsoft SQL Server Transact-SQL Reference.
For more information about performing a database load, see the LOAD statement in the Microsoft SQL Server Transact-SQL Reference.
For more information about performing a database backup, see the DUMP statement in the Microsoft SQL Server Transact-SQL Reference.
This example compares the output of a SELECT statement before and after sp_coalesce_fragments is run. Comments delimited by two hyphens (--) are interspersed throughout this example.
SELECT
'dbname'=substring(db_name(ug.dbid),1,10)
,ug.segmap,ug.lstart -- page number within a database
,ug.size,ug.vstart -- page number within dv
,'(vstart+size)' = ug.vstart + ug.size
,'device'=substring(dv.name,1,10)
FROM
master.dbo.sysusages ug,master.dbo.sysdevices dv
WHERE ug.vstart between dv.low and dv.high AND
dv.status & 2 = 2 --physical disk
AND
db_name(ug.dbid) in ('DB1','DB22','db_junk3')
ORDER BY ug.vstart
This is the results set:
dbname segmap lstart size vstart (vstart+size) device ---------- ------ ------ ---- ----------- ------------- ---------- DB1 3 0 2048 352321536 352323584 dvdata1 DB22 3 0 1024 352323584 352324608 dvdata1 DB22 3 1024 1024 352324608 352325632 dvdata1 DB22 4 2048 1024 369098752 369099776 dvlog1 db_junk3 7 0 1024 369099776 369100800 dvlog1 DB22 4 3072 1024 369100800 369101824 dvlog1 DB1 4 2048 1024 369101824 369102848 dvlog1 DB1 4 3072 768 369102848 369103616 dvlog1
Note In the results set the size of 768 for one of the DB1 fragments is not an even multiple of 512. These fragment sizes are difficult to mimic when building compatible work databases for dump and load activities.
Compare the two results sets after sp_coalesce_fragments is executed:
EXECUTE sp_coalesce_fragments
This is the results set:
dbname segmap lstart size vstart (vstart+size) device ---------- ------ ------ ---- ----------- ------------- ---------- DB1 3 0 2048 352321536 352323584 dvdata1 DB22 3 0 2048 352323584 352325632 dvdata1 DB22 4 2048 1024 369098752 369099776 dvlog1 db_junk3 7 0 1024 369099776 369100800 dvlog1 DB22 4 3072 1024 369100800 369101824 dvlog1 DB1 4 2048 1792 369101824 369103616 dvlog1
The number of sysusages fragments is reduced by two.
Note The two DB22 fragments that have segment map 4 cannot be coalesced because they are not contiguous. The lpage 1029 for database DB22 was in a coalesced fragment, and note that its corresponding segmap value remained 3 (system tables and data usage). Similarly, lpage 3081 in DB1 remained on a fragment where segmap is 4 (log usage).
Only the system administrator can run this procedure.