Restricting DB storage growth within AWS instance

Question

Post reply

Restricting DB storage growth within AWS instance

johnnyrmtl

Hall of Fame

Points: 3505
More actions
June 10, 2021 at 5:34 pm

#3894556

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

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

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

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.

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

johnnyrmtl Hall of Fame Points: 3505 More actions · Answer 2

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

Mr. Brian Gale SSC-Insane Points: 24689 More actions · Answer 3

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?

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

johnnyrmtl Hall of Fame Points: 3505 More actions · Answer 4

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 ?

Mr. Brian Gale SSC-Insane Points: 24689 More actions · Answer 5

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.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

johnnyrmtl Hall of Fame Points: 3505 More actions · Answer 6

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