Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: How to reduce database file size.

    We have a database which was created with an initial file size of 10 gig. Currently it is only using 2 gigs.

    We have developers that want to have a copy of the database on their desktops, but do not have 10 gigs free space.

    What is the best way to get them a copy of the database while reducing the footprint?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm assuming you don't want to do DBCC SHRINKDATABASE and DBCC SHRINFILE?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I would go for generating a script of all the objects, creating a realistic sized DB on the laptop, then using DTS or some such to bring the data over.

    It is a bit of a pain when you are dealing with a lot of tables. Especially since Enterprise Manager tends to do things alphabetically, instead of the way you have your foreign keys set up.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah, we could create a new database on the developers desktop and then DTS everything over. But the general question, I guess, is whether it is possible to shrink a database file below its initial size.

    I believe SHRINKDATABASE and SHRINKFILE will only reduce down to the initial size of the file when it was created.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    That's true of SHRINKDATABASE; however, SHRINKFILE will let you shrink lower than the original size. You just need to specify the exact size. For example:

    DBCC SHRINKFILE('Northwind_Data',300)
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    One caution, though is that dbcc shrinkfile will take a bunch of time if it needs to move a lot of data around. This will also cause the transaction log to grow (which is kind of ironic, eh?). Start off with dbcc shrinkfile (1, truncateonly). This will chop off any of the file that is above the "high water mark" of the data, and is relatively quick.

    EDIT: If memory serves, the only limit to the shrinkage is the size of the model database.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How many objects are we talking about btw?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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