Post reply

Page Fragmentation

  • January 23, 2004 at 4:42 pm

    #107302

    First a statement: when you backup and restore a database, you can reclaim diskspace that is lost to page fragmentation. I think this is true, but can someone confirm this?

    Now a question: how does one go about diagnosing page fragmentation? Can this be achieved using the System Monitor/Performance application?

    Thanks.

  • January 23, 2004 at 5:20 pm

    #491489

    Not sure about the backup and restore ?, but you can use dbcc showcontig to get a feel for the fragmentation in tables.

  • January 25, 2004 at 11:11 am

    #491530

    I believe if you have 50% page frags on a table and you restore it you will have the same, haven't tested it but I don't think restoring does any good or bad for your fragmentation. Steve's suggestion of dbcc showcontig is good, you might also want to monitor your page splits/sec counter with system monitor to see if you have poorly placed indexes and or just need to adjust fill factors.

     

    HTH

    ------------

    Ray Higdon MCSE, MCDBA, CCNA

  • January 25, 2004 at 11:35 am

    #491534

    I agree with the Backup / Restore statemnts above. Not sure, I believe you can actually use the backup file and attach it as the DB, In other words, the backup is pretty much just a file copy of the original database. If however you backup, then remove the origianl DB, then defrag the HD, then restore you will most likely have decreased the O/S file fragmentation, since it should restore contiguously. It's probably best if backup file and DB file are on different HDs. May want to do this kind of operation after you've addressed the internal data fragmentation 1st.

    Not quite "on-topic", but just some thoughts.



    Once you understand the BITs, all the pieces come together

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply