PRB: SQL Server Does Not Detect the Addition of the 33rd Segment

• Microsoft SQL Server versions 4.2x, 6.0, 6.5
• Microsoft SQL Server, Enterprise Edition, version 6.5

SYMPTOMS

SQL Server databases are limited to 32 device segments. SQL Server should prevent the database from expanding beyond 32 device segments. However, in some cases, an entry is added to the sysusages system table before an error is returned.

If this happens, when SQL Server is restarted, the database will fail recovery with the following error:

Error: 909, Severity: 21, State: 1 More than 32 entries required to
build the logical-virtual translation table for database 'xxx'. The
database is too fragmented.

WORKAROUND

If the database needs to be enlarged, use the sp_coalesce_fragments stored procedure. This stored procedure, which is only available in SQL Server version 6.5, consolidates the entries in sysusages. So if two or more segments share the same data device and are logically located next to each other on the segment, SQL Server will map a larger segment on the data device, thereby replacing the two smaller segments.

However, in some cases, this procedure will not consolidate segments. For example, if you have 32 segments on 32 individual data devices, then sp_coalesce_fragments cannot consolidate any sysusages mappings.

Additional query words: fragments db

Keywords :
Version : WINNT:4.2x,6.0,6.5
Platform : winnt
Issue type : kbprb