• To try and answer your first question:

    If you only have a few rows (couple could be a few) in a table, SQL creates those pages on an extent which is shared by other pages. An extent is 64k, page 8. If you have a table with narrow rows and only a couple hundred rows, the chance is good you using a mixed extent. With this in mind, SQL may not defrag it because a) it's so small, b) there's other data sitting on the extent.

    Try adding a couple thousand rows to the table (Assuming you can) and see if that changes things.

    See BOL, pages and extents for more info.

    To your second question, yes.

    When SQL does a restore, it restores all tables. One of them being sysindexes. Sysindexes determines on what pages / extents sit. DUring a restore, SQL acquires a chuck of disk space. It then creates all the extents / pages. Once done, it starts writing out the data page by page. (or extent by extent?).

    Restoring a backup will only give you contiguous OS space, not within the SQL files.

     

     

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!