Post reply

SQL Server 2000 Reclaim "Unused" Space

  • June 1, 2005 at 10:43 am

    #163848

    First let me state that this is NOT related to the log file. Rather this is related to the MDF/Data File.

    I have a 22 GB database that shows 13 GB of unused spaced in the data file. I have tried reclaiming some of this using DBCC SHRINKFILE. I cannot get this space back. I have read all of the related threads on this topic that I could not find a solution.

    I have tried using a simple shrink. I have tried using NOTRUNCATE followed by another run with TRUNCATEONLY.

    I have tried DBCC CLEANTABLE in case there was space to get back from dropped VARCHAR columns.

    I cannot regain this space and I don't know why. Does anyone have any ideas? I've defragged the indexes and then done the shrink to no avail.

    What am I missing?

    Thanks,

    George

  • June 1, 2005 at 10:47 am

    #562268

    This is just a hunch... is it possible that the db size if set to a certain fixed number?.. can't think of anything else to help you in this situation..

  • June 1, 2005 at 10:59 am

    #562272

    Thanks for the reply. Unfortunately the database is set to AutoGrow with no restriction on size. I am just really stumped. I have never run into this.

  • June 1, 2005 at 11:10 am

    #562277

    Me neither... I never had to deal with big databases. I'll let the other dbas take this one.

  • June 1, 2005 at 12:51 pm

    #562323

    By any chance, did you have a large amount of BLOB data that you've deleted? If so, the only way to reclaim that space is to BCP the data out and back in. SQL Server 2005 will fix this.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • June 1, 2005 at 1:21 pm

    #562331

    That is a possibility. I will have to check with the developers. But I do know what there are an awful lot of text and image columns througout the database. Thanks for the hint!

    George

  • June 1, 2005 at 1:23 pm

    #562332

    Is there a way for me to verify that this is the problem or do I just need to go by the symptoms that I am seeing?

    Thanks again,

    George

  • June 1, 2005 at 1:26 pm

    #562336

    Given that you have tried everything else in mind, and that you have no other idea, then I'd go for it unless it can cause problems for the users.

  • June 1, 2005 at 1:50 pm

    #562340

    Problem is this is a productin loan system. On top of that there are so many tables with blobs that I wouldn't know where to start! Was just hoping I could show people that this is why we haven't been able to reclaim space.

  • June 1, 2005 at 2:02 pm

    #562351

    Maybe you can ask the developpement team if a blob column has been dropped lately. That would be your best bet to get that info, unless you have a log reader that can check for alter table statements.

    or maybe Frank has a way to check this out.

  • June 1, 2005 at 3:13 pm

    #562366

    http://snipurl.com/fata

    is a most recent thread.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • June 2, 2005 at 9:35 am

    #562605

    Thank you. Very good thread for this topic. Thanks so much.

    George

Viewing 12 posts - 1 through 11 (of 11 total)

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