Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2011
    Posts
    1

    Unanswered: Reached the limit of # of device fragments

    Hello,

    Trying to altering one of the databases, I have reached number of allowable fragments that can be altered to any given database...

    ========================

    Msg 5028, Level 16, State 1:
    Server 'SERVER123', Line 1:
    ALTER DATABASE failed. This command will not make changes to a database that
    would cause the number of device fragments to exceed the maximum of 128.
    Recreate the database and try again.

    =============================

    I tried to drop/recreate the database but devices are too small in size (1G/2G) to make any difference in allocation. So, I dropped all the devices pertaining to the database, worked with UNIX admin to create larger chunck size devices (8G) and recreated database with larger size, but here comes the trouble - when I reloaded the database from the previous backups, I have inherited the layout of the database how it was originally dumped. Of course, there were several devices that had "data" and "log" together, which is a big NO-NO! So, I tried to cleanup. I sp_dropped from "logsegment" data devices and alter "log" on separate device. Is there a better way of handling this situation when you increase device size? Please let me know if you have any other suggestions.

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Ensure you create the data and log fragments in the same sequance
    e.g. original
    data 100
    log 50
    data 100
    data 100
    data 300
    log 100

    Create new as
    Data 100
    Log 50
    Data 500
    Log 100

    else as you say change all to data and create a new log

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •