Restricting DB storage growth within AWS instance

  • Hello all,

    We have an issue where within an RDS instance the tempdb recreates more files when FULL even when the growth is set to a limited size.

    Which setting disables this option as we do not want the TEMPDB to grow beyond it's LIMIT.

    Thank you

    JR

  • What IS the limit you're trying to set it to, how many files does it have, and how big is your largest table/index?

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Hello,

    Thanks for replying...

    I am new to AWS and did not set up the instance, the problem is that when the TEMPDB grows even though the existing files are capped ( each a certain size capped )  AWS storage automatically creates new TEMP files allowing for more growth.

    I hope i'm clear as we are just trying to limit the TEMPDB to a certain size.

    Is this a setting within AWS policies ?

    JR

  • My guess here is that you didn't configure TEMPDB correctly because if you set a MAX SIZE on TEMPDB, that's as big as it will go.

    Even the AWS documentation indicates that putting a MAX SIZE value is useful if you don't want TEMPDB to grow out of control:

    https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.TempDB.html

    To quote that article - "To prevent the tempdb database from using all available disk space, set the MAXSIZE property."

    Are you sure that the AWS TEMP files that are being automatically created are being used for TEMPDB?

  • Let me get more details of what's going on

    I hear that auto-scaling will make the storage grow regardless of the setting ,is this true ?

  • I expect that auto-scaling would make the AWS space grow, but it shouldn't make TEMPDB grow past the limits you put in place.

  • That is indeed what I think so not sure what's going on,

Viewing 7 posts - 1 through 7 (of 7 total)

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