Microsoft KB Archive/193097

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

Article ID: 193097

Article Last Modified on 12/3/2003

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition
 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 6.5 Enterprise Edition

-



This article was previously published under Q193097



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: kbprb KB193097

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.