Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Mar 2008
    Posts
    33

    Unanswered: shrinkdatabase taking forever...

    Hi all,

    2 weeks ago I deleted about 200GB of data from a 300GB+ database. It's a custom DB we want to use to test few things. We wanted a smaller size DB for our testing and since we didn't have any we grabbed a production backup, removed sensitive data and ran a large archiving script on it... Anyway so far so good but our data file was still the same size as before.

    So we started a shrinkdatabase... it has been running for 2 weeks now! After about 1 week I interrupted the shrinkdatabase process and ran a
    dbcc shrinkdatabase('DB', truncateonly)
    just to see if the data file will get reduced a bit or not. It did get reduced by about 20GB. I assume that
    dbcc shrinkdatabase('DB', 0)
    has free up enough pages at the end of the data file so a truncateonly was able to free up some space... Anyway after this we started the
    dbcc shrinkdatabase('DB', truncateonly)
    again... still running...

    The database was never shrank before and every index is highly fragmented... Is that why it's taking so long? Am I actually going to have to wait for another few weeks before that thing finishes??

    Anyone has experience running shrink on large DBs?


    thanks!

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The general consensus on shrinking database files is "don't".

    If you really need to shrink the database, here is what you would need to do:

    1) Export all of the data with either bcp or DTS to a temporary location
    2) Truncate all tables in the database.
    3) Shrink the database to the desired size
    4) Re-import all of the data exported in step 1

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    eh, why bother

    bcp (not DTS) the data out in native format
    take the database script, without constraints and rebuild the database (as in create a new one)
    bcp the data in
    Apply the constraints
    Done
    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.

  4. #4
    Join Date
    Mar 2008
    Posts
    33
    yeah shrinkdatabase doesn't seem to be friend with most DBAs...

    our initial intention was to save 'man hours' by running an existing archiving script (and making it purge data instead of archiving) and then run a shrinkdb... that way we didn't have to custom design DTS packages or something else of the kind. Never I would have though shrinkdatabase would take so long... who knows it might run for another month... at this point I don't know what to expect. I mean.... 2 weeks on a dedicated machine!

    well unless there isn't any better trick to shrink database size we will wait few more days since we don't have much time right now... and if the shrink isn't completed then we will look at your suggestion,

    I hate shrink....

  5. #5
    Join Date
    Mar 2008
    Posts
    33
    bulk copy program..., that sounds interesting, I never used that but that might be faster then dealing with DTS

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    if you need a blank, why not use the "script out the database" feature in the EM (2k) and SSMS(2k5) and use that script to create a blank small copy. Copy over any data you might need. Done.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not used it but this guy is a good dba so likely to be decent:
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Mar 2008
    Posts
    33
    thanks! this script works perfectly, (so far anyway... still going)

    at least now I can see the data file size slowly going down whereas before I was left in the dark with no idea if it would take another 1-2 days or 7-8 weeks... Since it seems to be working I will leave it running, although it's taking much longer to execute then if I was doing a bcp or dts, at least I don't have to spend time writing scripts or so,


    thanks again

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    "Since it seems to be working...."

    Are you serious
    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.

  10. #10
    Join Date
    Mar 2008
    Posts
    33
    Are you serious
    yes... my MDF is going down by chunk of 50MB every now and then, it's crazy slow don't get me wrong... but considering that I was running database shrink for over a week without seeing even 1MB gain it's an improvement... It might overall be slower then doing it in one chunk but at least now I see some improvements and can estimate how long it will take. Also if the machine restarts or something I wouldn't want to restart from scratch (although I think I wouldn't have to cuz I read somewhere that you can interrupt a shrink and it will restart where it left... but I don't know if it's true or not)

  11. #11
    Join Date
    Mar 2008
    Posts
    33
    FYI in 2 hours my MDF went down by 150MB... if I can reduce the file size by another 150GB I will be happy... lol... I will give you guys an update in few months... or if I need this done quicker I will reconsider the bcp suggestion...

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Does your database contain any heaps?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Mar 2008
    Posts
    33
    if you are referring to tables without clustered indexes there are plenty of them in this database... for some odd reasons whoever designed this thought that it would make sense to make all the archive tables heap tables, some of these tables have 25-30 large columns spread over 100 millions rows +...

    little update on the shrink script, over night my MDF was reduced by almost 75GB... still going

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    IIRC heaps and database shrinks do not go well together. I have forgotten the finer details TBH.

    I assume that they have (hopefully) no indexes at all right? Assuming the designer skipped these as (s)he thought they would slow down inserts - check out this thread:
    http://www.dbforums.com/showthread.php?t=1620836
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Mar 2008
    Posts
    33
    thanks, will read the thread,

    for some strange reasons the large archvie tables have no clustered indexes but they do have anywhere between 3 to 8 nonclustered on them...

Posting Permissions

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