Results 1 to 7 of 7

Thread: Shrink Tables

  1. #1
    Join Date
    Mar 2004
    Posts
    31

    Unanswered: Shrink Tables

    Ok. Thanks for the help today with helping me isolate why I have 2 almost identical databases, one is 11GB and one is 33GB.

    I was able to find out that a few of the tables are way off in size proportions. In one database, the file is 354 MB, and the new DB, the same file is 8.9 GB!! The row count is identical and the number of columns is the same.

    I tried dropping the table and an doing a full refresh, so my database is smaller, but there is still a lot of free space that won't go away when I shrink the DB.

    1. Does anyone know the code to shrink each table one at a time?
    2. Does anyone know how to remove the free space in my DB, the shrinkDB function is just not working.

    Thanks.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How much of the space is data, and how much is log? Are the log settings the same on both databases?

    Have you used DBCC SHRINKDB, or better yet DBCC SHRINKFILE on it? If you have lots of time, consider DBCC INDEXDEFRAG.

    -PatP

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What's your recovery model?

    Do you have scheduled maintenance?
    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.

  5. #5
    Join Date
    Mar 2004
    Posts
    31

    Answers

    Pat,

    Here are my stats now:

    Data:
    10665 MB Used 19144 MB Free

    Log:
    4163 MB Used o MB Free

    The log settings are the same on both DB's.
    I used the shrinkdatabase function, not the shrinkfile, and as you can see my free space is so immense.

    What I need to do is get rid of most of the free space of the data, and shrink the Tlog if possible.

    Brett,

    Not sure what you mean by recovery model. I do have maintenance plans scheduled, but only to backup the DB twice a dat and shrink the Tlog at night.

    Thanks.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Recovery model is Full, simple or bulk logged

    I usually have it set to full, and then dump the db at 3:00am, and then dump the logs every 15 minutes...

    And I don't set the files to unlimited growth...I have maintenance set up weekly sunday night to take care of it...

    And an alert in case the files get to 85% of max...
    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.

  7. #7
    Join Date
    Mar 2004
    Posts
    31
    My recovery model is full.

    I shrank the transaction log with the following statement and it only took a few seconds to get rid of 4GB:

    BACKUP LOG ancosalesdm WITH TRUNCATE_ONLY
    DBCC SHRINKFILE (ancosalesdm_log, 2)

    However, I am now trying to shrink the 19 GB from my DB and am unsuccessful. I am running the following statement, but it keeps running forever, should I just wait it out? How long could it take to complete? Am I using the right SQL to do this?

    DBCC SHRINKFILE (ancosalesdm_data)

Posting Permissions

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