Post reply

resize db files

  • August 2, 2004 at 7:05 am

    #83514

    I took over a database that has 2 datafiles that are 4.7 and 3.8 gig each.  I want to reconfigure the data to more managable file size (2048) which is also our standard.  I know how to do this in Oracle, export the data, recofingure the files in tables spaces and import the data again.

    What is the best way to do this in SQL Server 2000? This is a 7 day a week system, but I have from 1:00am to 6:00am to do the work.  I do have a test server to play with.

     

    Thanks,

    Joseph

  • August 2, 2004 at 8:24 am

    #517268

    Been there with this one.  I have a question before I start to ramble...How many tables and indexes are you dealing with?


    "Keep Your Stick On the Ice" ..Red Green

  • August 2, 2004 at 8:26 am

    #517269

    Curious why you htink 2048 is more manageable?

    To do this, if you have free space, you can execute a dbcc shrinkfile to lower the size of the existing databases. I'd do that first. Then if you need more space, create 1 or 2 more data files, make them close to the size or 2048 that you need. Don't have them autogrowing since that is a relatively expensive process.

    Now you need to move data over there. If you move a clustered index to a datafile, the data moves, so in EM (the easy way) you can specify a new data file for your clustered index and the data will move. Once you have enough stuff moved (migth need to experiment), you can shrink the first files.

     

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • August 2, 2004 at 8:32 am

    #517272

    Steve...I think they have a standard of 2048.  I think it kind of takes off of the Oracle Extent concept (and OS block size).   I used to have my SQL Server files set to some factor 8 because of the page sizes..After doing some math I realized it was kind of irrelevant.

     

    Enterprise Manager would be great for a few tables, but not thousands.


    "Keep Your Stick On the Ice" ..Red Green

  • August 2, 2004 at 9:15 am

    #517285

    2048 is our standard.  In some cases we end up zipping down the files and ftp them from one site to another.  This is basically a convience and standards issue.  I don't have a techincal issue with the size of the files.

    There are 720 tables and 1398 indexes.  We don't have a cluster.  Straigh 0+1 raid.

     

    thanks,

    Joseph

  • August 2, 2004 at 9:29 am

    #517288

    Do you have clustered indexes?


    "Keep Your Stick On the Ice" ..Red Green

  • August 2, 2004 at 9:44 am

    #517291

    Yes.  We use clusterd primary keys.  Where the primary key is not clustered, we generaly have a compound index clustered on fields that are more commonly used in the "where" clauses.

     

    Joseph

Viewing 7 posts - 1 through 6 (of 6 total)

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