Post reply

To remove external fragmentation

  • May 28, 2002 at 10:45 pm

    #104917

    How to minimize external fragmentation without any tool as we dont have any fragmentation tool?

    I think of this scenerio....

    Here in my case we have three filegroup and each filegroup is having 3 data files.

    For example I will take Filegroup 1(On Raid 5)

    I add one more datafile(datafile 4) on a different Raid drive with the size equal to three datafiles.

    Now I empty datafile 1 and delete it.

    Now I empty datafile 2 and delete it.

    Now I empty datafile 3 and delete it.

    Now all the data in new datafile.

    Now I create three data file again on the same old raid array with the same size

    Now I empty datafile 4 and delete it.

    This way all the data from datafile 4 will be shifted to other three datafiles and there should not be any external fragmentation

    Will this work? or anything wrong with this approach

    There may be some obvious details which I might forget to put. But main question: will this particular approch works or not?

    Please don't suggest to buy or use any fragmentation tool.

    Prakash

    Prakash Heda
    Lead DBA Team - www.sqlfeatures.com
    Video sessions on Performance Tuning and SQL 2012 HA

  • May 29, 2002 at 5:24 am

    #429883

    Although not advised it should work. But to make sure you don't have problems with it you should make sure you create the files large enough to avoid doing this often. For example if you know the files will grow 1GB in a month then have each file large enough to accomidate this fact so that this is a once a month need. Also if you have any tables that are static (change rarely) consider creating a seperate filegroup for these and generate them on the drive first then the rest this will help you limit some data movement. However, whether you want to or not you really should use a defrag utility instead of this as you are increasing the chance for data loss and possible residual issues unless you are formating the drive after you remove the files or some utility to 0 empty sectors and run checkdsk to look for file fragements taht sill exist. Another way you possible could do this is to do a file backup then delete the original files and restore back (after you grow the files for future needs), this then has a two fold effect, you create a backup and you accomplish the same basic task as you are now trying to do.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Viewing 2 posts - 1 through 1 (of 1 total)

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