Shrink database issue

  • In my database, I have few temp tables that total up to 50GB in size. I already removed the data from the table. When I set the shrinkdatabase in my maintenance plan, it runs successfully but doesn't shrink the database.

    I tried dbcc shrinkfile(1,truncateonly) and got the same result.

    However, from sql management studio when I right click the database and select shrink database, it takes a while and shrinks the database. I notice the file size reduces by 50GB.

    my question is why it's not doing this with the maintenance plan shrink option or with the sql command?

    Is there any other command that I could use to shrink it?

    Appreciate any help on this.

  • Best would be not to shrink the main db at all.  If possible, add a secondary filegroup, create the temp tables in there, then shrink only the file(s) in that secondary filegroup.

    For your shrinkfile command, don't specify "truncateonly"; either specify the known size in mb you want to shrink to or leave it off for max shrink:

    DBCC SHRINKFILE(1)

    I can't help you on the maintenance plan shrink, since I've never used a mp.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who give you the shirt off *someone else's* back.

  • Scheduling a database shrink is a bad idea and should be avoided.  There are times when performing a manual shrink may be necessary - but it should only be done when you know the database will never grow to utilize that additional space.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Let's ask the question... how often do the "temp tables" appear?  Let's also ask, why do you have "temp tables" of such a size to begin with?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden wrote:

    Let's ask the question... how often do the "temp tables" appear?  Let's also ask, why do you have "temp tables" of such a size to begin with?

    The reason I'm asking these questions is because it sounds like you may be concentrating on the wrong issue to begin with.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • All tables in the database should keep data only for 15 days. There is an automated task by the software vendor to delete data older than 15 days. But this process doesn't release the space back to OS. And it keeps growing and our network admin keeps adding additional space on the hard drive. With the deletion of old data, db size should not grow beyond 20GB. Since the space is not returned to OS, last time db size was 120GB and growing.

    I want to shrink the database to release the space back to OS.

  • That makes it sound like the data isn't being "deleted" in the database, then.  While the files wouldn't shrink, the space IN the DB would be marked as "free" space and the next time one of those temp tables gets spun up it SHOULD use said free space.

    Think of your database file like a sheet of paper that gets data written to it, when you "delete" data, that erases data from the paper, which you can now write on again.

    You probably should reach out to the software vendor to find out why their process isn't doing what it should and why the database just keeps on growing if they're "deleting" data...

  • Thanks for the response. Will contact the vendor.

  • It could also be that that data isn't being deleted in the same order as the Clustered Index.  If that's true, you can recover the space left by the deletes simply by rebuilding the Clustered Index.  That should leave only a small amount of extra space from the rebuild while returning space for use from the tables themselves.

    For sure, most non-clustered indexes aren't going to be in the correct order for painless deletes and will also need to be rebuilt.

    Don't look for logical fragmentation when trying to decide which tables/indexes need to be rebuilt due to the deletes.  Especially for indexes in a different order than the deletes, there may be zero or very little logical fragmentation.  Look for  at the percent of page fullness, instead.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Thanks for the suggestions. Much appreciated.

Viewing 10 posts - 1 through 9 (of 9 total)

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